20

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

hakre
  • 193,403
  • 52
  • 435
  • 836
Eran Galperin
  • 86,251
  • 24
  • 115
  • 132
  • 1
    For anyone finding this question and want to read up on the subject: http://stackoverflow.com/questions/2532729/daylight-saving-time-and-timezone-best-practices – Industrial Sep 16 '11 at 22:14

5 Answers5

13

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.

Misha Moroshko
  • 166,356
  • 226
  • 505
  • 746
Amr Mostafa
  • 23,147
  • 2
  • 29
  • 24
  • 1
    I actually prefer to store it in the server's timezone and let PHP timezone aware functions to deal with the offset calculation. My problem is that the timestamp is not saved in numeric form which PHP wants for the date() function – Eran Galperin Dec 08 '08 at 19:30
  • We too use the same solution after some research. Would like to here alternatives people have used. – Sean Dec 16 '08 at 19:47
7

Since PHP 5.2 you can use DateTime which makes working with timezones easy:

$datetime = new DateTime($dbTimestamp, $timezone);
echo $datetime->format('Y-m-d H:i:s');
$datetime->setTimezone(new DateTimeZone('Pacific/Nauru'));
echo $datetime->format('Y-m-d H:i:s');
John Conde
  • 217,595
  • 99
  • 455
  • 496
1

You could try forcing MySQL to use UTC everywhere using SET time_zone.

Unfortunately I haven't got any answer for the strtotime/UNIX_TIMESTAMP thing, in fact I've got the same problem with Postgres.

  • Maybe we should appeal to the SQL standards committee to enforce using a numeric timestamp? ;) – Eran Galperin Dec 06 '08 at 21:31
  • Nah, I'm sure datetime columns are there for a good reason. I just haven't figured it out yet... –  Dec 06 '08 at 21:40
0

I didn't found any elegant solution online so I have created a Timezone HTML select generator script and here's the output directly. It's something like this:

<select name="timezone" id="timezone">
    <optgroup label="UTC -11:00">
        <option value="Pacific/Midway">UTC -11:00 Midway</option>
        <option value="Pacific/Niue">UTC -11:00 Niue</option>
        <option value="Pacific/Pago_Pago">UTC -11:00 Pago_Pago</option>
    </optgroup>
    <optgroup label="UTC -10:00">
        <option value="America/Adak">UTC -10:00 Adak</option>
        <option value="Pacific/Honolulu">UTC -10:00 Honolulu</option>
        <option value="Pacific/Johnston">UTC -10:00 Johnston</option>
        <option value="Pacific/Rarotonga">UTC -10:00 Rarotonga</option>
        <option value="Pacific/Tahiti">UTC -10:00 Tahiti</option>
    </optgroup>
    . . . . . . . . . . . . . .
    <optgroup label="UTC +13:00">
        <option value="Pacific/Apia">UTC +13:00 Apia</option>
        <option value="Pacific/Enderbury">UTC +13:00 Enderbury</option>
        <option value="Pacific/Fakaofo">UTC +13:00 Fakaofo</option>
        <option value="Pacific/Tongatapu">UTC +13:00 Tongatapu</option>
    </optgroup>
    <optgroup label="UTC +14:00">
        <option value="Pacific/Kiritimati">UTC +14:00 Kiritimati</option>
    </optgroup>
</select>

Enjoy!

Lucian D.
  • 95
  • 5
0

What I have been doing currently with my PHP websites are this -

  1. Enforce UTC/GMT on the application everywhere.
  2. Avoid using auto generated timestamp columns on the database ends, instead push date/datetime from PHP scripts.
  3. Since you cannot fetch Time zone from client using PHP, I have used JavaScript to re-render the datetime coming from backend onto the UI based on user's current Time zone.
Tunir
  • 31
  • 5