Timezones with PHP & MySQL

[JasonLewis]

So the most relevant thread I found was here, I just want to get a little more information.

 

In an application I'm developing, a user may decide to change their timezone. Currently I'm setting my timezone to GMT with date_default_timezone_set. However I just realized that when inserting date into MySQL DATETIME fields it is (quite obviously) using the server time.

 

So what is the best method here.

 

[*]Use SET GLOBAL time_zone = timezone; as indicated in the MySQL manual.

[*]Change my field types to TIMESTAMP instead, from what I've read it converts it to UTC (which in essence is GMT) before storing in the database. After testing this though, it still inserts NOW() as the servers date/time. So not sure what the go is there.

[*]Store a UNIX timestamp in the database, and convert it before display with PHP.

 

I'm at a loss here as to what would be the cleanest, and most suitable solution for an application that depends heavily on the timezone so it can accurately display dates and time.

 

Cheers.

[btherl]

Have you tried #1?  If it works, it sounds like the best solution.  If mysql and php agree on timezone then everything is easy, and you just need to convert times when you're displaying them to the user.

 

We actually run our db servers in AEST timezone which has daylight savings, and it makes things painful   I definitely wouldn't recommend that.

[JasonLewis]

I was originally going to use #1, I just wasn't to sure on how widely available that solution is with other databases.

 

Time is such a bitch when it comes to programming. If all else fails I guess I could just use gmdate.

 

I went against your recommendation, because I'm living in Victoria. DST for the win!

[JasonLewis]

Apologies for double post. Been researching it further, and the general consensus is to convert to a neutral timezone, UTC/GMT to be more precise. I do not wish to leave the task up to MySQL, as the first option I mentioned above requires SUPER privileges and I do not wish to assume that one would have them.

 

The safest option would be creating a time class, that handles your date/time. Convert dates to GMT prior to inserting into the database, then convert them to the users local timezone prior to display.

 

That, at least to me, seems to be the preferred option. Of course if you do have SUPER privileges then assigning your database to your timezone would be a much easier solution.

[simshaun]

I store UNIX timestamps in INT fields in the database (method #3), using UNIX_TIMESTAMP() instead of NOW().

 

I use PHP to do the timezone conversion (date_default_timezone_set)

[JasonLewis]

I store UNIX timestamps in INT fields in the database (method #3), using UNIX_TIMESTAMP() instead of NOW().

 

I use PHP to do the timezone conversion (date_default_timezone_set)

 

Yup, another common method. I've been looking at the DateTime and DateTimeZone classes PHP offers, from what I can see they don't do a very good job.

[joel24]

had the same problem n wrote a little script that I include in every page,

//timezone php
putenv('TZ=Australia/Sydney');

//get offset, including daylight savings!
$currentOffset = "+".(date("Z") / 60 / 60).":00";

//timezone mysql
$update_tz = @mysql_query("SET time_zone = '$currentOffset'") or die(mysql_error());