Dealing with timezones

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
User avatar
Eran
DevNet Master
Posts: 3549
Joined: Fri Jan 18, 2008 12:36 am
Location: Israel, ME

Dealing with timezones

Post by Eran »

Some issues with timezones in PHP have been in the back of my mind for a while now, and I was wondering what each of you do to solve it.

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 using:

Code: Select all

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:

Code: Select all

$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 timestamp 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
alex.barylski
DevNet Evangelist
Posts: 6267
Joined: Tue Dec 21, 2004 5:00 pm
Location: Winnipeg

Re: Dealing with timezones

Post by alex.barylski »

I personally don't store date or times as anything but an integer timestamp.

Then all I do is set timezone at script startup according to users locale/timezone and format using locale aware date() function -- custom implementation uses ICU methods.

date() should do the trick for the most part when you pass in a timestamp it'll generate and show the time according to the local timezone.

Cheers,
Alex
User avatar
Eran
DevNet Master
Posts: 3549
Joined: Fri Jan 18, 2008 12:36 am
Location: Israel, ME

Re: Dealing with timezones

Post by Eran »

I personally don't store date or times as anything but an integer timestamp.
But then you can't use MySQL date functions and comparisons..
alex.barylski
DevNet Evangelist
Posts: 6267
Joined: Tue Dec 21, 2004 5:00 pm
Location: Winnipeg

Re: Dealing with timezones

Post by alex.barylski »

What kind of comparisons do you need to do exactly, that cannot be done using a timestamp?

Code: Select all

 
$time_then = 3600; // One hour after epoch
$time_now = time();
 
// Is today greater then yesteryear
if($time_then < $time_now){
  echo 'Its past that date <!-- s:) --><img src=\"{SMILIES_PATH}/icon_smile.gif\" alt=\":)\" title=\"Smile\" /><!-- s:) -->';
}
It has the added benefit of being more efficient than relying on MySQL date comparison because there is no conversion needed their are just integer timestamps.

I'm interested to know what kind of comparisons you are doing?

Cheers,
Alex
User avatar
Eran
DevNet Master
Posts: 3549
Joined: Fri Jan 18, 2008 12:36 am
Location: Israel, ME

Re: Dealing with timezones

Post by Eran »

I meant comparisons and functions inside queries
alex.barylski
DevNet Evangelist
Posts: 6267
Joined: Tue Dec 21, 2004 5:00 pm
Location: Winnipeg

Re: Dealing with timezones

Post by alex.barylski »

Why couldn't you compare timestamps when they are integers?

Code: Select all

$sql = "SELECT * FROM table WHERE time > $time_old"
I am slightly intrigued now by your approach... :)
User avatar
Eran
DevNet Master
Posts: 3549
Joined: Fri Jan 18, 2008 12:36 am
Location: Israel, ME

Re: Dealing with timezones

Post by Eran »

That of course will work. But what about:

Code: Select all

 
SELECT * FROM table WHERE YEAR(timestamp_column) == '2006'
 
Or:

Code: Select all

 
SELECT * FROM table WHERE DAYOFWEEK(timestamp_column) == 1 //Return only sundays
 
The last one especially would be a chore without date functions
alex.barylski
DevNet Evangelist
Posts: 6267
Joined: Tue Dec 21, 2004 5:00 pm
Location: Winnipeg

Re: Dealing with timezones

Post by alex.barylski »

I never used SQL for much except quick storage and retreival...but this is interesting...

[sql]SELECT * FROM TABLE WHERE YEAR(timestamp_column) == '2006'[/sql]

Would have to become:

Code: Select all

 
$time_start = time() - NUMBER_OF_DAYS_FROM_DEC_31;
$time_finish = time() + NUMBER_OF_DAYS_UNTIL_JAN_1;
 
$sql = "SELECT * FROM table WHERE timestamp > $time_start AND timestamp < $time_finish";
 
Is there not a timestamp conversion routine in MYSQL to convert from Integer to Internal format?

Cheers,
Alex
User avatar
Eran
DevNet Master
Posts: 3549
Joined: Fri Jan 18, 2008 12:36 am
Location: Israel, ME

Re: Dealing with timezones

Post by Eran »

Is there not a timestamp conversion routine in MYSQL to convert from Integer to Internal format?
There is (FROM_UNIXTIME() ), but it obviously incurs extra overhead. But I see what you're getting at - if most of my queries require the timestamp in integer format, it might be better to store it that way and convert it when needed. I'm not sure if that's feasible but I'll be sure to have a look at it.

I would also be happy to hear suggestions for solutions that allow me to keep timestamps in the native mysql format (why did they have to change from unix timestamps.. :( )
User avatar
Eran
DevNet Master
Posts: 3549
Joined: Fri Jan 18, 2008 12:36 am
Location: Israel, ME

Re: Dealing with timezones

Post by Eran »

Anyway has any suggestions on how to approach timezones while using the native MySQL timestamp format?
Post Reply