Timezone Strategies

Not for 'how-to' coding questions but PHP theory instead, this forum is here for those of us who wish to learn about design aspects of programming with PHP.

Moderator: General Moderators

User avatar
Weirdan
Moderator
Posts: 5978
Joined: Mon Nov 03, 2003 6:13 pm
Location: Odessa, Ukraine

Re: Timezone Strategies

Post by Weirdan »

kaisellgren wrote:Is there a reason why not to use public timezone servers? They are highly reliable and you may have a list of different servers in case someone happens to be down.
If you mean ntp servers, they surely have their uses (they tell what time is it now) but it's irrelevant in the context of this discussion.
alixaxel
Forum Newbie
Posts: 15
Joined: Thu Mar 12, 2009 9:00 am

Re: Timezone Strategies

Post by alixaxel »

This is a hard problem, specially if you want to maintain (and convert) the correct time between Timezones/DST, but since this is not the case let me just say that GMT does have DST, and GMT is different from UTC (this one is DST-free).
User avatar
kaisellgren
DevNet Resident
Posts: 1675
Joined: Sat Jan 07, 2006 5:52 am
Location: Lahti, Finland.

Re: Timezone Strategies

Post by kaisellgren »

Weirdan wrote:If you mean ntp servers, they surely have their uses (they tell what time is it now) but it's irrelevant in the context of this discussion.
Fair enough.
alixaxel wrote:This is a hard problem, specially if you want to maintain (and convert) the correct time between Timezones/DST, but since this is not the case let me just say that GMT does have DST, and GMT is different from UTC (this one is DST-free).
Really? UTC does not "include" DST?
André D
Forum Commoner
Posts: 55
Joined: Thu Aug 28, 2008 7:03 pm

Re: Timezone Strategies

Post by André D »

Some of the problems that have been raised in this thread can be solved by relying on functionality that is built into MySQL. (I can't speak for PostgreSQL.)

When you store timestamps, you can store them in TIMESTAMP columns and MySQL will automatically convert the time from the current time zone to UTC for storage. This feature gets overlooked by many developers because the time stamp values are automatically converted from UTC back to the current time zone when you retrieve them.

To leverage this functionality for time stamp localization, set MySQL's per-connection time zone to the current user's zone like this: "SET time_zone = timezone"

So for a very basic example, you can store the value of NOW() in a TIMESTAMP column and rest assured that MySQL has normalized it to UTC. Later, when you go back to retrieve it for a user in Helsinki, call "SET time_zone = 'Europe/Helsinki'" and then when you run your SELECT query, MySQL will localize the time for Helsinki.

Keep in mind that the supported time zones are system-dependent, and it's important to keep your server current with the latest time zone information.

Read these sections in the MySQL manual for more information:
MySQL Server Time Zone Support
Staying Current with Time Zone Changes
TIMESTAMP Properties

Other points worth noting:
  • This UTC normalization only occurs for TIMESTAMP columns, not DATE, TIME or DATETIME columns.
  • The fact that TIMESTAMPs are stored this way makes data highly portable. If you move to a server in a different time zone, your previously-stored timestamps are still correct after you migrate.
  • TIMESTAMP columns require 4 bytes, DATETIME columns require 8 bytes.
User avatar
kaisellgren
DevNet Resident
Posts: 1675
Joined: Sat Jan 07, 2006 5:52 am
Location: Lahti, Finland.

Re: Timezone Strategies

Post by kaisellgren »

Sometimes I hear developers saying that it does not really matter whether you use columns requiring 4 bytes or 8 bytes. In my opinion, this is bollocks. Imagine a web board with million users. They each have their own signup date and birthday. The difference between 4-bytes and 8-bytes column is rather big. With a 8-bytes column, the total size used for dates is 1000000*2*8/1024/1024=15.25 megabytes, however, with a 4-bytes column, the total size would be 1000000*2*4/1024/1024=7.62 megabytes. So, for a one million member sized community, the choice between 4 or 8 bytes made a difference of 7½ megabytes! This DB space was wasted just for date values... and just for the member table date values... so, if you can, use 4-bytes over 8-bytes.
alixaxel
Forum Newbie
Posts: 15
Joined: Thu Mar 12, 2009 9:00 am

Re: Timezone Strategies

Post by alixaxel »

kaisellgren wrote:
alixaxel wrote:This is a hard problem, specially if you want to maintain (and convert) the correct time between Timezones/DST, but since this is not the case let me just say that GMT does have DST, and GMT is different from UTC (this one is DST-free).
Really? UTC does not "include" DST?
As far as my research can tell, no. UTC stands for Universal Coordinated Time and therefore it does not make use of DST, only offsets (like when you convert between timezones), in other words there is no summer or winter time in UTC. Also UTC normally has the same time as GMT (not GMT on DST, which I think is named something like GST), and in UTC one minute can have 59 or 61 seconds, this happens about four times a year in average and it serves to correct the imperfect (because of gravity and other forces) Earth's ellipse around the sun, otherwise in a "gazillion" years it would be day at midnight. :P

I've coded a simple function (which I haven't tested extensively) for converting between timezones.

Code: Select all

 
<?php
 
date_default_timezone_set('UTC');
 
function Timestamp_Helper($format = 'U', $time = 'now', $from = null, $to = null, $modifier = null)
{
    $result = date_create($time, timezone_open(date_default_timezone_get()));
 
    if (is_null($from) === false)
    {
        $result = date_create($time, timezone_open($from));
    }
 
    if (is_a($result, 'DateTime') === true)
    {
        if (is_null($to) === false)
        {
            date_timezone_set($result, timezone_open($to));
        }
 
        if (is_null($modifier) === false)
        {
            date_modify($result, $modifier);
        }
 
        return date_format($result, $format);
    }
 
    return false;
}
 
echo Timestamp_Helper(DATE_RSS, 'now', 'GMT', 'America/New_York');
echo Timestamp_Helper(DATE_RSS, 'now', 'Europe/Lisbon', 'Europe/Madrid');
 
?>
 
Maybe it'll be useful for someone.
André D
Forum Commoner
Posts: 55
Joined: Thu Aug 28, 2008 7:03 pm

Re: Timezone Strategies

Post by André D »

kaisellgren wrote:Really? UTC does not "include" DST?
UTC itself does not adjust for daylight savings time (DST). Instead, timezones themselves adjust their UTC offset. For example, in the Pacific time zone, they use Pacific Standard Time (PST) which is UTC-8, when areas of that time zone switch to DST they move from PST to Pacific Daylight Time (PDT), which is UTC-7.
alixaxel wrote:in UTC one minute can have 59 or 61 seconds, this happens about four times a year in average and it serves to correct the imperfect (because of gravity and other forces) Earth's ellipse around the sun, otherwise in a "gazillion" years it would be day at midnight. :P
These are called leap seconds. I don't think they happen quite as often as you say, but they're very interesting none-the-less. The MySQL documentation explains how MySQL handles leap seconds.
User avatar
kaisellgren
DevNet Resident
Posts: 1675
Joined: Sat Jan 07, 2006 5:52 am
Location: Lahti, Finland.

Re: Timezone Strategies

Post by kaisellgren »

André D wrote:For example, in the Pacific time zone, they use Pacific Standard Time (PST) which is UTC-8, when areas of that time zone switch to DST they move from PST to Pacific Daylight Time (PDT), which is UTC-7.
Does GMT include DST? At least that's what I think after reading alixaxel's post. If GMT does include it, then I think DST is the main difference between UTC and GMT.
André D
Forum Commoner
Posts: 55
Joined: Thu Aug 28, 2008 7:03 pm

Re: Timezone Strategies

Post by André D »

kaisellgren wrote:Does GMT include DST? At least that's what I think after reading alixaxel's post. If GMT does include it, then I think DST is the main difference between UTC and GMT.
No; Greenwich Mean Time (GMT) has nothing to do with daylight savings. GMT is often used interchangeably with Coordinated Universal Time (UTC). GMT used to be used as the international time reference, but it was replaced by UTC.

You should read these two Wikipedia articles:
http://en.wikipedia.org/wiki/Greenwich_Mean_Time
http://en.wikipedia.org/wiki/Coordinated_Universal_Time
User avatar
kaisellgren
DevNet Resident
Posts: 1675
Joined: Sat Jan 07, 2006 5:52 am
Location: Lahti, Finland.

Re: Timezone Strategies

Post by kaisellgren »

alixaxel wrote:since this is not the case let me just say that GMT does have DST, and GMT is different from UTC (this one is DST-free).
I guess that is incorrect then.

Great, I would be confused otherwise :)
alixaxel
Forum Newbie
Posts: 15
Joined: Thu Mar 12, 2009 9:00 am

Re: Timezone Strategies

Post by alixaxel »

kaisellgren wrote:
alixaxel wrote:since this is not the case let me just say that GMT does have DST, and GMT is different from UTC (this one is DST-free).
I guess that is incorrect then.

Great, I would be confused otherwise :)
No, like Andre D very well said, UTC expresses timezones and it's respective summer/winter times as offsets from the same "timezone" of standard/winter GMT, this forum (phpBB) for instance has an bug where it says that "All times are UTC [ DST ]", instead it should read "All times are GMT [ DST ]" because UTC is DST agnostic. GMT on the other hand has it's correspondent DST timezone (GST or BST I believe) which is UTC-1.

It's confusing at first but if you read the Wikipedia entries Andre mentioned about you will understand how it all works, the best way to deal with dates is to always grab the date and on the local user timezone and convert/store it in UTC, then you can convert it again to the timezone your user prefers.
alixaxel
Forum Newbie
Posts: 15
Joined: Thu Mar 12, 2009 9:00 am

Re: Timezone Strategies

Post by alixaxel »

arborint wrote:
volomike wrote:- MySQL's timestamp format is not compatible with Unix/Epoch, so a tool to flip to/from that might help?

- The same as all of the above, but takes Unix/Epoch timestamps as the input parameter instead of strings
Anyone have code for those? :)
MySQL to PHP
$php = strtotime($mysql);

PHP to MySQL
$mysql = [gm]date('YmdHis', $php);
User avatar
kaisellgren
DevNet Resident
Posts: 1675
Joined: Sat Jan 07, 2006 5:52 am
Location: Lahti, Finland.

Re: Timezone Strategies

Post by kaisellgren »

I'm slow and bad at reading English so I do not feel like reading Wikipedia.

Can someone answer to this. My friend from NYC is GMT-5 and I am GMT+2 so I am 7 hours ahead of his time, but now, however, he went to summer time so is he UTC-4 while I am UTC+2? Or in other words, is he GMT-5+DST and I am GMT+2 (no DST)?
alixaxel
Forum Newbie
Posts: 15
Joined: Thu Mar 12, 2009 9:00 am

Re: Timezone Strategies

Post by alixaxel »

kaisellgren wrote:I'm slow and bad at reading English so I do not feel like reading Wikipedia.

Can someone answer to this. My friend from NYC is GMT-5 and I am GMT+2 so I am 7 hours ahead of his time, but now, however, he went to summer time so is he UTC-4 while I am UTC+2? Or in other words, is he GMT-5+DST and I am GMT+2 (no DST)?
That is possible, the schedule for the DST isn't global and changes with the location, and in some places like Australia, Brazil and Israel they also change in certain years.

The difference between your friend at NY and you (lets say you are in Germany) can be [5], 6, 7 or [8] hours, depending on the DST combinations.
User avatar
kaisellgren
DevNet Resident
Posts: 1675
Joined: Sat Jan 07, 2006 5:52 am
Location: Lahti, Finland.

Re: Timezone Strategies

Post by kaisellgren »

Okay here are my thoughts...

Whether to store values in numbers or in readable text, I don't know, but here's a demonstration of the numerical approach:

Someone posts on the forums and the post will get the time value for instance:

Code: Select all

$datetime = 1236439969;
A user from Finland will read the post:

Code: Select all

date_default_timezone_set('Europe/Helsinki');
echo 'Helsinki GMT+2 (no DST): '.date('jS F Y H:i:s',$datetime).'<br>';
And the time is displayed correctly. Then a user from NY views the same post:

Code: Select all

date_default_timezone_set('America/New_York');
echo 'NY GMT-5 (no DST): '.date('jS F Y H:i:s',$datetime).'<br>';
And the time is displayed correctly.

It even works with DSTs. Try running this:

Code: Select all

header('content-type: text/plain');
 
$datetime = 1236439969;
 
echo "INFO: NY = GMT-5, Finland = GMT+2\n\n";
 
echo "\nA guy from Finland views the post:\n";
date_default_timezone_set('Europe/Helsinki');
echo 'Helsinki GMT+2 (no DST): '.date('jS F Y H:i:s',$datetime);
 
echo "\nA guy from NY views the post:\n";
date_default_timezone_set('America/New_York');
echo 'NY GMT-5 (no DST): '.date('jS F Y H:i:s',$datetime);
 
echo "\nOne day later... when DST is in effect in NY, but not in F.\n";
$datetime += 60*60*24; // One day later...
 
echo "\nA guy from Finland views the post:\n";
date_default_timezone_set('Europe/Helsinki');
echo 'Helsinki GMT+2 (no DST): '.date('jS F Y H:i:s',$datetime);
 
echo "\nA guy from NY views the post:\n";
date_default_timezone_set('America/New_York');
echo 'NY GMT-5 (DST is now in effect "+1H"): '.date('jS F Y H:i:s',$datetime);
 
echo "\nOne month later when both NY and F have DST in effect.\n";
$datetime += 60*60*24*30; // onemonth latere
 
echo "\nA guy from Finland views the post:\n";
date_default_timezone_set('Europe/Helsinki');
echo 'Helsinki GMT+2 (DST is now in effect "+1H"): '.date('jS F Y H:i:s',$datetime);
 
echo "\nA guy from NY views the post:\n";
date_default_timezone_set('America/New_York');
echo 'NY GMT-5 (DST is now in effect "+1H"): '.date('jS F Y H:i:s',$datetime);
Results:
INFO: NY = GMT-5, Finland = GMT+2


A guy from Finland views the post:
Helsinki GMT+2 (no DST): 7th March 2009 17:32:49
A guy from NY views the post:
NY GMT-5 (no DST): 7th March 2009 10:32:49
One day later... when DST is in effect in NY, but not in F.

A guy from Finland views the post:
Helsinki GMT+2 (no DST): 8th March 2009 17:32:49
A guy from NY views the post:
NY GMT-5 (DST is now in effect "+1H"): 8th March 2009 11:32:49
One month later when both NY and F have DST in effect.

A guy from Finland views the post:
Helsinki GMT+2 (DST is now in effect "+1H"): 7th April 2009 18:32:49
A guy from NY views the post:
NY GMT-5 (DST is now in effect "+1H"): 7th April 2009 11:32:49
To me that seems to work correctly. The time is displayed correctly according to correct DST and timezone values.
Post Reply