452353 - Bugzilla should store all times and dates as UTC

Status: NEW

(Bugzilla :: Bugzilla-General, enhancement)

Description

[Max Kanat-Alexander]

Now that we display dates and times according to the user's timezone, there's a very strange thing that will happen if the server changes its time for daylight savings time--all times displayed may or may not be off by an hour.

In addition, there are other problems with storing the local time, particularly if Bugzilla moves from one timezone to another. (Also, we've always had that "one strange hour" problem during DST changes, where comments appear earlier than other comments because they were added inside the one hour where daylight savings is changing.)

The best solution to this problem (and the standard one used by all major web applications) is to store all dates and times in the database as UTC, and convert them when we display them.

Before doing this, we would have to also make Bugzilla interpret all *input* datetimes (such as when searching or setting deadlines) according to their set timezone. (That would be a separate bug.)

Comment 1

[Max Kanat-Alexander]

We will, of course, have to fix all dates and times in the database as part of this change, during checksetup. That can most easily be done by subtracting or adding a number of minutes using SQL.

The Deadline field will of course not be adjusted, as it's just a date.

Comment 2

[Frédéric Buclin]

(In reply to comment #0)
> very strange thing that will happen if the server changes its time for daylight
> savings time--all times displayed may or may not be off by an hour.

Is it really the case? DateTime is supposed to know if your server changed its time due to daylight saving time, and convert times accordingly. (When I view bug comments locally, I can see that comments do not have the same timezone depending on the date of creation of these comments, depending of the DST.)


> In addition, there are other problems with storing the local time, particularly
> if Bugzilla moves from one timezone to another.

How often does this happen? And have you any guarantee that at the time you do the conversion, the server has not already been moved?


 (Also, we've always had that
> "one strange hour" problem during DST changes, where comments appear earlier
> than other comments because they were added inside the one hour where daylight
> savings is changing.)

That was before we use DateTime. Is this still valid with DateTime?



> Before doing this, we would have to also make Bugzilla interpret all *input*
> datetimes (such as when searching or setting deadlines) according to their set
> timezone.

I somehow disagree with this. Think about Mozilla; I'm sure everybody sets dates and times with PST/PDT in mind, not their own timezone, so values they enter are already based on PST/PDT, and using the user's timezone would be a mistake.

Comment 3

[Max Kanat-Alexander]

(In reply to comment #2)
> Is it really the case? DateTime is supposed to know if your server changed its
> time due to daylight saving time, and convert times accordingly.

  Yes, that's exactly the problem. We don't store timezones in the database, so when DateTime starts adjusting for DST, all times will be wrong.

> That was before we use DateTime. Is this still valid with DateTime?

  Yes, it will still happen. We haven't changed a single thing about the dates we store in the database.

> I somehow disagree with this. Think about Mozilla; I'm sure everybody sets
> dates and times with PST/PDT in mind, not their own timezone, so values they
> enter are already based on PST/PDT, and using the user's timezone would be a
> mistake.

  If they set their timezone explicitly in their preferences, we should use their timezone. If they're using the server's timezone, we should use the server's timezone. People currently input everything in the server's timezone because that's all Bugzilla supports, so that's a null argument.

Comment 4

[Frédéric Buclin]

too late for 3.4.

Comment 5

[bjoern]

any nows on this? We're about to update our bugzilla and see all this timezone proboblems during our tests. No fixes for it so far?

Comment 6

[Frédéric Buclin]

(In reply to comment #5)
> any nows on this? We're about to update our bugzilla and see all this timezone
> proboblems during our tests. No fixes for it so far?

Which timezone problems? (I'm in #samba-technical as LpSolit, FYI)

Comment 7

[bjoern]

I mean the the timezone issues that are mentioned in the comments of this bug. In bugzilla2.samba.org the system time is correct syncronized by NTP, /etc/timezone is UTC, apache is running with "unset TZ".

See the timezone issue here:

https://bugzilla2.samba.org/show_bug.cgi?id=7121#c25

Björn Jacke 2011-02-27 18:54:26 UTC

... This comment was added at 18:52 CET (17:52 UTC). What will bugzilla show?

A generic saving of UTC timestamps inside the database would probably be very helpful to easy timezone issues in general.

Comment 8

[bjoern]

the problem seem to be that bugzilla uses the time zone identifier that bugzilla/apache itself is runnig in while it uses the timezone adjusted timestamps that mysql gives back without taking a look at the timezone that mysql actually uses.

So you are always in a broken timezone situation when the timezone of the system was modified while mysql runs.

You are also in a broken timezone situation with bugzilla if you think that you want to run bugzilla in a different timezone and write some TZ value in /etc/apache2/envvars or so.

Having different timzones in different environments on one and the same machine is not so unusual. Any user can adjust his own TZ value to overrride what /etc/localtime says. So if an administrator that uses a TZ value which results in a UTC-0500 time then bugzilla will suddenly also run with this kind of timezone offsets everywhere and at the same time tell that it is using UTC.

Comment 9

[bjoern]

here is an example from a test bugzilla installation: illustrating the issue



Björn Jacke 2011-02-27 22:03:32 UTC                   Comment 3

test3 done after this:

root@samba-bugzilla:~# TZ=Europe/Athens date
Sun Feb 27 22:02:26 EET 2011
root@samba-bugzilla:~# TZ=UTC date
Sun Feb 27 20:02:37 UTC 2011
root@samba-bugzilla:~# export TZ=Europe/Athens
root@samba-bugzilla:~# /etc/init.d/mysql stop
Stopping MySQL database server: mysqld.
root@samba-bugzilla:~# /etc/init.d/mysql start
Starting MySQL database server: mysqld.

Comment 10

[bjoern]

you can enforce mysql to use a certain timezone from the client site using SET time_zone='+01:00' to indicate GMT+1 for example. Something like this should be done in any case, whether you switch to UTC completely or not.

Comment 11

[Max Kanat-Alexander]

(In reply to comment #8)
> the problem seem to be that bugzilla uses the time zone identifier that
> bugzilla/apache itself is runnig in while it uses the timezone adjusted
> timestamps that mysql gives back without taking a look at the timezone that
> mysql actually uses.

  You are correct. Your MySQL server and webserver must be in the same timezone.

  Doing SET TIME ZONE is a possibility; I've thought about it before, but there were problems with it (that I don't remember now) in practice when I used it.

Comment 12

[Max Kanat-Alexander]

Oh, but I did want to make clear that I agree that we should fix the behavior you're describing, Bjoern. Essentially you are to some degree describing why we want to store information in UTC. This bug is still open and valid, and it would be nice to fix it one way or another.

Comment 13

[Tom Dickson]

This affects me; I have a large installation that is currently in PST/PDT and I want to move the server to UTC to make it match my other servers; if I do so, all the dates and times become wrong (8:43 PDT becomes 8:43 UTC).

I can't even find UTC in the Preferences dropdown for changing the visible timezone (which would work around this issue)

Comment 14

[Frédéric Buclin]

We are going to branch for Bugzilla 4.4 next week and this bug is either too invasive to be accepted for 4.4 at this point or shows no recent activity. The target milestone is reset and will be set again *only* when a patch is attached and approved.

I ask the assignee to reassign the bug to the default assignee if you don't plan to work on this bug in the near future, to make it clearer which bugs should be fixed by someone else.

Comment 15

[ublackman]

We are seeing this bug as well and would be nice to have a fix.
We're using Amazon Web Services (AWS) and our EC2 web server is on Pacific time, but the RDS database server is on UTC. 
When date is stored into RDS, it gets translated based on timezone setting of RDS
when CGI reads from DB it uses webserver timezone, so all the emails and times are in the future.

Comment 16

[Dave Miller [:justdave]]

Here's how to convert the existing data in the database when we get around to this:

https://blog.mozilla.org/it/2012/11/16/converting-timezone-specific-times-in-mysql/

Comment 17

[Casey Becking]

I also have this bug with AWS - RDS. I assume as usage of those two grows and more people are using them for bugzilla this issue will grow as well.

Comment 18

[Casey Becking]

No progress on this bug?

Comment 19

[:glob ✱]

this also causes issues when we do date math on a database which doesn't fully understand dst.

for example, a token issued at "2014-03-06 02:47:53 PDT" causes issues with MAX_TOKEN_AGE set to 3 days when using mysql.

when working with tokens we ask the database to perform the date math, such as "SELECT issuedate + INTERVAL 3 day ..".  in this instance mysql (v5.6.12) returns "2014-03-09 02:47:53", which is an invalid date.


(In reply to Casey Becking from comment #18)
> No progress on this bug?

unfortunately it isn't a simple thing to fix.  i'd love to get some time to put against this issue, but that's unlikely.

Comment 20

[Casey Becking]

Completely understand, just figured I'd ask since I'm seeing the issue as well. Thank you.