Bug 1260827 - Migrate times in the DB from EST to UTC
Migrate times in the DB from EST to UTC
Product: Bugzilla
Classification: Community
Component: Database (Show other bugs)
Unspecified Unspecified
high Severity high (vote)
: ---
: ---
Assigned To: Jeff Fearn
Rony Gong
: 1260489 (view as bug list)
Depends On:
Blocks: 1095592
  Show dependency treegraph
Reported: 2015-09-07 21:36 EDT by Will Thames
Modified: 2016-08-05 04:19 EDT (History)
3 users (show)

See Also:
Fixed In Version: 5.0.3.rh3
Doc Type: Bug Fix
Doc Text:
Story Points: ---
Clone Of:
Last Closed: 2016-08-05 04:19:26 EDT
Type: Bug
Regression: ---
Mount Type: ---
Documentation: ---
Verified Versions:
Category: ---
oVirt Team: ---
RHEL 7.3 requirements from Atomic Host:
Cloudforms Team: ---

Attachments (Terms of Use)

  None (edit)
Description Will Thames 2015-09-07 21:36:36 EDT
Description of problem: 
Bugzilla database has no timezone information, and all timestamps are in EST/EDT.
This means that the DB server and the web server also need to be in EST/EDT which is a very bad server configuration (as timestamps go backwards during the switch back from daylight savings)

Version-Release number of selected component (if applicable):

How reproducible: Entirely

Steps to Reproduce:
1. Change web and DB servers to be non EST/EDT
2. Create a new bug or comment
3. See that the creation time does not match the actual time

Actual results:
Timestamps don't reflect reality

Expected results:
Web and DB server can be in any arbitrary timezone and timezone information is appropriately converted at storage and display time

Additional info:
Comment 1 Jason McDonald 2015-09-08 00:54:22 EDT
Notes for Development and QE:

* The Errata tool is very sensitive to timestamps in Bugzilla. The syncing process for bug data asks Bugzilla for a list of bug ID's for bugs that have changed since the last successful sync.  ET then fetches the detail of those bugs.  If the timestamps are not handled correctly, ET will either sync no bugs (which will block advisories from progressing in their lifecycle) or will sync far too many bugss (which will likely cause severe performance issues for ET).

* Bugzilla propagates bug data to Jira and Salesforce (SFDC).  Any timestamps in that data must be verified to propagate correctly.

* The yet-to-be-activated message bus integration include a timestamp in every message.  Once that code is switched on in production, ET will consume changes from the message bus rather than polling Bugzilla periodically. Inaccurate timestamps in those messages may have a similar impact on ET as described above.

* Other users run saved searches and RPC queries to ask for various types of changes that have occurred within a specific time window.  Some of those query results are used to drive critical business processes, such as triaging of security issues and RHEL bugs, so again those search results must be accurate.

* The rules engine makes use of timestamps in both displays and in the rollback function.  These must be verified to work correctly.

* Timestamps must also be verified to propagate to Teiid correctly.
Comment 2 Jason McDonald 2015-09-08 00:56:50 EDT
Note for SysOps:

* Any change to the system timezone of Bugzilla should be accompanied by a review of the start times of cron jobs.  Some of these were originally chosen to run outside peak usage periods to lessen their impact on performance.
Comment 3 Jason McDonald 2015-09-11 03:16:07 EDT
*** Bug 1260489 has been marked as a duplicate of this bug. ***
Comment 4 Jeff Fearn 2015-09-14 22:41:56 EDT
Suggest moving this to 5.x as it's an intrusive change and is likely to complicate the migration.
Comment 5 Jeff Fearn 2015-09-15 00:20:53 EDT
The task here is to change the timestamps from EST +4 to UTC, the web and database servers must all be using UTC as part o this change.
Comment 6 Rony Gong 2015-10-18 07:06:29 EDT
QA environment(bzperfweb01.app.qa) with version(4.4.10042-1, DB: psql)
Result: Pass
1.Execute the scripts of this bug fix, 
2.Both change the timezone to 'UTC' in web and db server.
==>Check some bugs's creation_ts and comment_time keep same as before from web UI, I compare it with current production.
==>Create a new bug, add some comments and do some update to this bug. check that all operation time record right.
Comment 7 Jason McDonald 2015-10-19 03:30:36 EDT
How long did the script take to run in the QE environment?  This information will be useful for planning the deployment of this change.
Comment 8 Rony Gong 2015-10-19 03:49:20 EDT
(In reply to Jason McDonald from comment #7)
> How long did the script take to run in the QE environment?  This information
> will be useful for planning the deployment of this change.

I execute this script in bzperweb01 which db dumped from production in Fed 2015, it cost around 45 mins,  I will try it in the rdu environment which is closer to current production, and leave comment then.
Comment 10 Jeff Fearn 2016-04-18 01:13:01 EDT
Hi Rony, did you get the times from RDU?
Comment 11 Rony Gong 2016-04-18 04:18:51 EDT
(In reply to Jeff Fearn from comment #10)
> Hi Rony, did you get the times from RDU?

On Rdu, It cost 48m49s.
I shut down all web servers.
Comment 12 Jeff Fearn 2016-06-17 00:24:15 EDT
Moving this back to assigned as it's not been tested as part of the BZ 5 upgrade, or integrated in to the BZ 5 code base.
Comment 13 Jeff Fearn 2016-06-17 01:08:09 EDT
The server must be using UTC tiem zone, if you used the bugzilla 5 ansible repo to configure the server this should already be done.


# ls -l /etc/localtime
lrwxrwxrwx. 1 root root 23 May 12 02:34 /etc/localtime -> /usr/share/zoneinfo/UTC

Script to run is bz5-ET2UTC.pl
Comment 14 Rony Gong 2016-06-20 22:35:33 EDT
This script cost almost 3 hours in qe test env(bz-web), and changed all time to 4 hours later, please confirm that this script mustn't applied to current BZ4.4 yet.

This script will only execute when BZ5 upgrade.

Note You need to log in before you can comment on or make changes to this bug.