Some difficulties with timezones in PHP happen to be at the back of my thoughts for some time now, and I'm wondering if you will find possible ways to deal with it than I am presently doing.

All the issues center around reformating database saved dates:

When confronted with a website that needs to support multiple timezones (for customers), to normalize the timezone offest of saved timestamps I usually store it using the server timezone while using CURRENT_TIMESTAMP attribute or even the NOW() function.

By doing this I do not have to consider what timezone was looking for PHP once the timestamp was joined (since PHP time functions are timezone aware). For every user, based on his preference I set the timezone somewhere during my bootstrap file using:

date_default_timezone_set($timezone);

When I am searching to format dates using the php date() function, some type of conversion needs to occur since MySQL presently stores timestamp within the format Y-m-d H:i:s. Without any regard to timezone, you can simply run:

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

The issue with this particular is the fact that date() and strtotime() are generally timezone aware functions, and therefore when the PHP timezone is placed in a different way in the server timezone, the timezone offset will apply two times (rather than once as we wish).

To cope with this, It's my job to retrieve MySQL timestamps while using UNIX_TIMESTAMP() function which isn't timezone aware, permitting my to use date() on it - therefore using the timezone offset only one time.

I do not enjoy this 'hack' when i can no more retrieve individuals posts when i would, or use * to fetch all posts (it sometimes simplifies queries greatly). Also, sometimes it's not a choice to make use of UNIX_TIMESTAMP() (particularly when using with open-source packages with little abstraction for query composition).

Another problem happens when storing the timestamp, when use of CURRENT_TIMESTAMP or NOW() isn't a choice - storing a PHP produced timestamp will store it using the timezone offset which I must avoid.

I am most likely missing something really fundamental here, but to date I've not had the opportunity to develop a normal means to fix handle individuals issues so I am instructed to treat them situation-by-situation. Your ideas are extremely welcome

Couple of several weeks ago we spent a while considering this. The strategy we wound up with is fairly simple:

  1. Store dates in GMT/UTC (e.g. timezone offset).
  2. Apply current user timezone offset after retrieval in the datebase (e.g. before showing towards the user or without notice).

We use unix timestamps format. But that does not matter.

You could attempt forcing MySQL to make use of UTC everywhere using SET time_zone.

Regrettably I've not got any answer for that strtotime/UNIX_TIMESTAMP factor, actually I have got exactly the same trouble with Postgres.