database - Dealing with timezones in PHP

Question

Some issues with timezones in PHP have been in the back of my mind for a while now, and I was wondering if there are better ways to handle it than what I'm currently doing.

All of the issues revolve around reformating database stored dates:

When dealing with a site that has to support multiple timezones (for users), to normalize the timezone offest of stored timestamps I always store it with the server timezone using the CURRENT_TIMESTAMP attribute or the NOW() function.

This way I don't have to consider what timezone was set for PHP when the timestamp was entered (since PHP time functions are timezone aware). For each user, according to his preference I set the timezone somewhere in my bootstrap file using:

date_default_timezone_set($timezone);

When I'm looking to format dates with the php date() function, some form of conversion has to take place since MySQL currently stores timestamp in the format Y-m-d H:i:s. With no regard to timezone, you could simply run:

$date = date($format,strtotime($dbTimestamp));

The problem with this is that date() and strtotime() are both timezone aware functions, meaning that if the PHP timezone is set differently from the server timezone, the timezone offset will apply twice (instead of once as we would like).

To deal with this, I usually retrieve MySQL timestamps using the UNIX_TIMESTAMP() function which is not timezone aware, allowing my to apply date() directly on it - thereby applying the timezone offset only once.

I don't really like this 'hack' as I can no longer retrieve those columns as I normally would, or use * to fetch all columns (sometimes it simplifies queries greatly). Also, sometimes it's simply not an option to use UNIX_TIMESTAMP() (especially when using with open-source packages without much abstraction for query composition).

Another issue is when storing the timestamp, when usage of CURRENT_TIMESTAMP or NOW() is not an option - storing a PHP generated timestamp will store it with the timezone offset which I would like to avoid.

I'm probably missing something really basic here, but so far I haven't been able to come up with a generic solution to handle those issues so I'm forced to treat them case-by-case. Your thoughts are very welcome

Answer 1

Few months ago we spent some time thinking about this. The technique we ended up with is pretty simple:

1. Store dates in GMT/UTC (e.g. 0 timezone offset).
2. Apply current user timezone offset after retrieval from the database (e.g. before showing to the user or whenever you want).

We use Unix timestamps format. But that doesn't matter.