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
volomike
Forum Regular
Posts: 633
Joined: Wed Jan 16, 2008 9:04 am
Location: Myrtle Beach, South Carolina, USA

Timezone Strategies

Post by volomike »

I'm going to follow this Timezone strategy on a site. If you have a better suggestion, please let me know.

1. I set the server time to UTC/GMT. I don't even bother with setting timestamp fields with a timezone in the PostgreSQL database because I will only read and write GMT (aka UTC) time.

2. When the user registers on the site, Javascript in their client browser will identify for me their timezone and perhaps the daylight savings time adjustment. I'll then push this into the database.

3. When the user logs in, the timezone and DST designation will be cached in a cookie used every time I display a date on data coming and going in the system.

4. The user will have a control panel setting in their dashboard area on the site where they can change the timezone and daylight savings that it has detected into something that they prefer.

I'll get more specific with actual code as I collect your feedback.
User avatar
volomike
Forum Regular
Posts: 633
Joined: Wed Jan 16, 2008 9:04 am
Location: Myrtle Beach, South Carolina, USA

Re: Timezone Strategies

Post by volomike »

The first step:

Code: Select all

// SET OUR SERVER TIME PROPERLY
// The Settings::TIMEZONE reads from our config file, and that's set to "UTC/GMT".
if (function_exists('date_default_timezone_set')) {
  date_default_timezone_set( Settings::TIMEZONE );
} else {
  putenv('TZ=' . Settings::TIMEZONE);
}
ini_set('date.timezone', Settings::TIMEZONE);
User avatar
volomike
Forum Regular
Posts: 633
Joined: Wed Jan 16, 2008 9:04 am
Location: Myrtle Beach, South Carolina, USA

Re: Timezone Strategies

Post by volomike »

In Javascript, it appears that all one needs to do is:

Code: Select all

var d = new Date();
var n = d.getTimezoneOffset()/60;
I also looked around and I think I see here that there's no need to get daylight savings time because the method above will always change based on DST in a given timezone, and this returns the offset against the current GMT, and GMT doesn't have DST, so there's no DST issues to contend with.
User avatar
volomike
Forum Regular
Posts: 633
Joined: Wed Jan 16, 2008 9:04 am
Location: Myrtle Beach, South Carolina, USA

Re: Timezone Strategies

Post by volomike »

In PostgreSQL, are there warnings about the accuracy of storing the timezone in a 'real' data type versus is a 'numeric' data type?

For now, a 'real' seemed suitable -- I'll just truncate off the extra digits I won't need. Besides, they'll be zeroes.
User avatar
volomike
Forum Regular
Posts: 633
Joined: Wed Jan 16, 2008 9:04 am
Location: Myrtle Beach, South Carolina, USA

Re: Timezone Strategies

Post by volomike »

It's dawning on me here that since the Javascript timezone stuff is not static and changes with the DST on the user's PC, I can't just load this into the database upon registration. Instead, it needs to be collected on every user login. But then that means the user's dashboard control panel setting shouldn't be something they can change. Instead, if they get their workstation time set properly, then the browser will experience this change, and then therefore pass me this change dynamically each time they login.

Even if I put an override, it serves me little good because with each login it needs to change based on their PC time.

Therefore, the only thing I need to do is collect it on login and cache it from there. No control panel requirement on the website necessary.
alex.barylski
DevNet Evangelist
Posts: 6267
Joined: Tue Dec 21, 2004 5:00 pm
Location: Winnipeg

Re: Timezone Strategies

Post by alex.barylski »

Why not just have the user select a timezone from a drop down list at signup and when they login set it like you are doing, using set_default_timezone_X()
User avatar
volomike
Forum Regular
Posts: 633
Joined: Wed Jan 16, 2008 9:04 am
Location: Myrtle Beach, South Carolina, USA

Re: Timezone Strategies

Post by volomike »

What's set_default_timezone_X()? I don't get the X and it didn't turn up on Google. If you meant changing the server time, well, I need to stick with the UTC/GMT server time because end users could be like all over the planet. Thus, all input and output in the database is in UTC/GMT, but is translated on the fly by one's browser designation, cached in a cookie.

If they select the timezone on registration, then it doesn't account for DST automatically and I have to figure that out on my end, which gets messy. Also, it's not like one can store that statically and have a change in the database via a control panel setting in the website -- this is because timezone stuff changes, and you don't want to have to put the onus on the end user to remember to make the control panel change. So, I thought, hmm, the browser does this for me, automatically, so just rely on that and cache it in a cookie as one goes from page to page.
User avatar
volomike
Forum Regular
Posts: 633
Joined: Wed Jan 16, 2008 9:04 am
Location: Myrtle Beach, South Carolina, USA

Re: Timezone Strategies

Post by volomike »

I noticed that this routine...

Code: Select all

var d = new Date();
var n = d.getTimezoneOffset()/60;
 
...is incorrect. I mean, it returns the opposite value of what you need. It returns a 5. However, if I go to Windows on my PC in a VM window, or Linux at command line with 'date -R', I get -0500 (-5, essentially). So, I had to change the routine like so:

Code: Select all

var d = new Date();
var n = (d.getTimezoneOffset()/60)*-1;
 
Now I get -5. And if I'm in a timezone like some part of China, I probably would get a positive number, which makes sense.
alex.barylski
DevNet Evangelist
Posts: 6267
Joined: Tue Dec 21, 2004 5:00 pm
Location: Winnipeg

Re: Timezone Strategies

Post by alex.barylski »

volomike wrote:What's set_default_timezone_X()? I don't get the X and it didn't turn up on Google. If you meant changing the server time, well, I need to stick with the UTC/GMT server time because end users could be like all over the planet.
Haha...sorry man...I actually meant (date_default_timezone_set) but was guessing at the name as I rarely call that function as I add it once in the bootstrap and never again use it.

I store everything as unix timestamps. When you format a timestamp using date() it will output the date/time according to the system timezone.

So if someone in Chicago(CST), created a record and the timestamp was based on CST, then at a later date, someone from Los Angeles viewed the record, so long as you set the timezone using the above mentioned function, date() would show the timestamp adjusted for the difference in time.

For example, say the record was created at 3PM CST by the individual Chicago and seconds later the user in LA viewed the record, the time should show 1PM, despite the timestamp itself being relative to CST

Cheers,
Alex
User avatar
volomike
Forum Regular
Posts: 633
Joined: Wed Jan 16, 2008 9:04 am
Location: Myrtle Beach, South Carolina, USA

Re: Timezone Strategies

Post by volomike »

PCSpectra wrote:...snip...I store everything as unix timestamps...snip...
Wow. That's ingenious. Now why didn't I think of that? Okay, so when you do sorting on these columns, that still works, and as long as you don't have dates before 1970, you're still okay, but selecting by date requires a conversion first, and there's no easy visual check of dates if you do it with your own eyes in the database table. But otherwise, it works great.

You see, that's why I like this forum.

So, does anyone else see a problem with PCSpectra's strategy of using Unix timestamps in the database, then using date() to convert to one's time based on knowing the timezone?
User avatar
volomike
Forum Regular
Posts: 633
Joined: Wed Jan 16, 2008 9:04 am
Location: Myrtle Beach, South Carolina, USA

Re: Timezone Strategies

Post by volomike »

In PostgreSQL, if you store the value in UTC/GMT in a field with a specification like...

Code: Select all

"dt_created" timestamp, --note we don't store timezone
...you can select the column like the following and extract the value back in Unix timestamp time (Epoch time, essentially).

Code: Select all

SELECT date_part('epoch', dt_created) AS dt_created FROM users
Problem # 1 -- I have to experiment with dates prior to 1970 to see what I end up with on PostgreSQL. I imagine negative numbers.

Problem # 2 -- I need to experiment on PostgreSQL and with PHP in interacting with PostgreSQL, and set the time past 2038. If I get a date before 2038, then it's got the Y2K38 bug because some knucklehead is running the date through a 32 bit data slot instead of a 64 bit data slot. Update: I see in the PostgreSQL docs that they use an 8 byte (64 bit) slot for storing a timestamp column, with or without the timezone.

Advantage # 1 -- By storing as a regular date, but in UTC/GMT format, and not adding the "with timezone" value afterwards on the column specification, you can now do visual inspection of a table to check dates.

Advantage # 2 -- By having your SELECT do the Unix timestamp translation on the fly, it probably runs far faster than PHP iterating with date() over the dates, converting to one's timezone. The disadvantage on that is that your SQL code isn't portable between database platforms such as going between PostgreSQL and MySQL and vice-versa.

Advantage # 3 -- You can also have PostgreSQL do the time conversion with the SELECT like so:

Code: Select all

SELECT dt_created, dt_created AT TIME ZONE INTERVAL '-05:00' FROM users
The first column returned will be in UTC/GMT time. The second column will be converted to -05:00 time, such as what you might see on the east coast of the USA.
Last edited by volomike on Tue Dec 30, 2008 9:25 pm, edited 2 times in total.
User avatar
Eran
DevNet Master
Posts: 3549
Joined: Fri Jan 18, 2008 12:36 am
Location: Israel, ME

Re: Timezone Strategies

Post by Eran »

I do, as I've detailed in this post from a couple of months ago. Not using the native MySQL date/time formats presents problems when you want to use date/time database functions. Alex suggested converting timestamps in real time, by I'm not sure what the performance implication will be. For now I'm sticking with the strategy I've outlined in that post.
User avatar
volomike
Forum Regular
Posts: 633
Joined: Wed Jan 16, 2008 9:04 am
Location: Myrtle Beach, South Carolina, USA

Re: Timezone Strategies

Post by volomike »

Pytrin, I updated my previous post, but I think you're right. I need to store in the regular YYYY-MM-DD HH:MM:SS format as UTC/GMT (no timezone necessary if stored in this format 100% of the time). In PHP that's "Y-m-d H:i:s". Then, I can get the date back in the end user's timezone time by doing this in PostgreSQL:

Code: Select all

SELECT dt_created, dt_created AT TIME ZONE INTERVAL '-05:00' FROM users
The first column gives you the time in UTC/GMT time. The second converted per a timezone of -05:00.

Now, in MySQL, I imagine there's a comparable technique.

Note also if you don't use the colon, as in "-0500" instead of "-05:00", you'll get a really screwed up time several days ahead.
User avatar
volomike
Forum Regular
Posts: 633
Joined: Wed Jan 16, 2008 9:04 am
Location: Myrtle Beach, South Carolina, USA

Re: Timezone Strategies

Post by volomike »

Javascript gives us a value like -5, -5.5, -4, 2, etc. But PostgreSQL wants to see this as like +00:00 or -05:30, etc. Here's a handy function example for converting it. I'm going with what I have, but perhaps you can make this even leaner?

Code: Select all

<?php
 
function ConvertTimezoneForDB($n) {
    $n = empty($n) ? 0 : $n;
    // GET OUR POSITIVE INTEGER VERSION
    $n1 = abs(intval($n));
    // GET OUR SIGN, POSITIVE OR NEGATIVE
    $s = ($n < 0) ? '-' : '+';
    // GET OUR FRACTION
    $n2 = abs($n - intval($n));
    // CONVERT FRACTION TO CLOCK MINUTES
    $n2 = $n2 * 60;
    $n2 = intval(str_replace('0.','',$n2));
    $n2 = sprintf('%02s',$n2);
    // CONNECT THE TWO IN TIMEZONE FORMAT
    $n = $s . sprintf('%02s',$n1) . ':' . $n2;
    return $n;
}
 
$i = 0;
$n[$i++] = 5;
$n[$i++] = 0;
$n[$i++] = '';
$n[$i++] = 5.5;
$n[$i++] = -6;
$n[$i++] = -6.4;
 
for ($a = --$i; $a >= 0; $a--) {
    echo 'Original Number: ' . $n[$a] . "<br />\n";
    echo 'Converted Number: ' . ConvertTimezoneForDB($n[$a]) . "<br />\n";
}
P.S. Don't forget that I have to do the Javascript value * -1 because for some reason Javascript returns the value in the opposite sign value than what you might expect.
User avatar
volomike
Forum Regular
Posts: 633
Joined: Wed Jan 16, 2008 9:04 am
Location: Myrtle Beach, South Carolina, USA

Re: Timezone Strategies

Post by volomike »

Little-known timezone facts. There's wild time stuff going on around Polynesia!

If you go from Kiribati, Christmas Islands (South Pacific) to Papeete, French Tahiti (also South Pacific), you jump from +14 to -10 hours UTC, and the distance is like the W-E (width) of Mexico. But wait! It gets better. Go switch Papeete with Alofi, Niue, which is between the South Pacific and Australia, and it's from +14 to -10 hours UTC, and the distance is also about the miles W-E (width) of Mexico.

So, you're looking at waking up in a hut in Kiribati and flying to Alofi, and spend perhaps a couple hours in the air, you land on an island 22 hours earlier in time. It's like you invented your own time machine. It's like landing on the island of the TV show, LOST.

SOURCE: http://www.timeanddate.com/worldclock/d ... .html?p1=0

Okay, I know... :offtopic:

Back on topic now...
Post Reply