Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Dealing with timezones in PHP

Tags:

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

like image 252
Eran Galperin Avatar asked Dec 06 '08 21:12

Eran Galperin


People also ask

What is PHP time zone?

The default timezone for PHP is UTC regardless of your server's timezone. This is the timezone used by all PHP date/time functions in your scripts.

How can I get time zone and country code in PHP?

PHP has a function for it using GeoIP. The geoip_time_zone_by_country_and_region() function will return the time zone corresponding to a country and region code combo.


2 Answers

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.

like image 97
Amr Mostafa Avatar answered Sep 30 '22 13:09

Amr Mostafa


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');
like image 20
John Conde Avatar answered Sep 30 '22 13:09

John Conde