GMT and DST

[devinemke]

i am designing a disitributed app that manages magazine submissions that uses mySQL as it's db backend. various tables in the app store temporal data. i am storing all date/times as GMT and have user preferences to adjust the date/time display for specific timezones. i have a config table where the user can set their timezone and their DST settings (similar to this forum). the timezone setting is just a positive or negative integer (east or west of GMT). the DST setting is a boolean field where the user can simply turn on/off the DST correction for their locale. i had hoped to do all of the date math at the db level rather than with PHP as i will be displying long lists of records with dates/times and did not want to run each date run thru a PHP function to do the adjustment. the timezone math is pretty simple:

$diff_minute = abs($config['timezone']) * 60;
if ($config['timezone'] > 0) {$dir = 'ADD';} else {$dir = 'SUB';}
$date_math = 'DATE_' . $dir . '(date_time, INTERVAL ' . $diff_minute . ' MINUTE)';
$sql = 'SELECT *, ' . $date_math . ' AS adjusted_date FROM table';

however for the DST adjustment i need to know if each date i'm pulling is within the DST window or not so i can add an hour or not. i realize that there is no real international standard for DST but for now i just wanted to deal with how DST is defined is most of the US. is there a mySQL or PHP function that can be fed a date string or timestamp and return whether that date in within DST or not? i realize that there is the "I" argument to the [man]date[/man] function but that does not apply here since it derives all of it's calculations relative to the the server's locale. i really wanted to avoid having to setup a table that maps out all of the DST shifts for the next 100 years. the must be a more elegant way to do this.

thanks.

[Sxooter]

Does MySQL support the "Timestamp with timezone" type?

The way it works is that all incoming timezones get converted to Zulu on input, and when a client asks for it back, it tells the database what timezone it's in, and the database does the work of conversion for the client.

PostgreSQL supports this type, and I can't imagine trying to reinvent the wheel to do it, cause like you mentioned, it's a complex job.

EDIT Part Deux:

OK, MySQL DOES support time zones, they just don't do it with timezone with timestamp types, i.e. all timestamps are with timezone (not zulu) and you can change the cllient timezone with "SET time_zone = timezone;" See this page:

time zone support

[devinemke]

hey Sxooter, thanks for the info. i read over that timezone bit in the manual but i'm still a bit confused about a few things:

1. as i mentioned, i'm allowing users to set their own timezones. on every page request i can simply set the timezone setting but i still don't see how that will affect any of the GMT dates/times when they are pulled out of the db. for example if the user set's his timezone for California i could do...

   mysql_query("SET time_zone = '-8:00'") or exit(mysql_error());
   $result = mysql_query('SELECT NOW()') or exit(mysql_error());
   echo mysql_result($result, 0);
   

   ...and this will in fact display the current time in California but it will have no affect on the GMT dates that are already in the db that need to pulled out and adjusted to California time...or am i missing something obvious here?

2. i still don't see how any of these timezone features in mySQL will solve my problem with the DST adjustment. as i said before, manually shifting all of the dates to a specfic timezone is the easy part. how do i know which dates are in the DST window or not.

3. it appears that all of that timezone stuff was added in mySQL 4.1.3 and i was hoping for a lower minimum system requirement with this app so i guess i would still have to do the date math manually anyway.

i'm begining to think that i will just have to map out the future DST shifts in a table and look it up for each date. i'm sure i'm not the first person to address this problem so i assume somewhere someone has a CSV or something with all of that data mapped out. even if it is mapped out it seems that it is certainly subject to change. George W Bush signed an energy bill last year that will change the DST shift in the US starting in 2007. i suppose anyone who is still using an old palm pilot or whatever will likely see the wrong date/time for part of next year.

thanks for the help.

[Sxooter]

In general, you can define a time zone as an offset (i.e. -08:00) or by the text name, like MDT etc. There are also longer names, depending on your time zone database on your system, like US/Chicago etc... using the numeric offset, of course, means you'll be handling DST adjustments yourself. Using the named type, generally means the DST change of an hour is handled by the system.

There's some more links on the MySQL page about timezones that points to some data used for it. note that timezone database info is integral to most linux systems, like redhat, and is updated when you do package updates. It's a question of making your db support them.

I really think MySQL 3.xx.yy is getting a bit long in the tooth. Setting a require of 4.0.xx (where xx is the latest patch level) isn't really setting the bar that high, and if you're worried about the GPL'd connect libs, you should be migrating to another database without such issues. Firebird, or postgresql are both good choices, postgresql generally has more developers working on it at a given time, and is my personal preference, but firebird generally is more forgiving to beginning DBAs than PostgreSQL is.

I'd suggest having a page where users can set a time zone from a drop down list if one isn't already set, and having a bool or something like it to show that the timezone has been set. note that the internal setting WILL be in GMT, not the one for the user. I.e. the user enters 12:00:00 US/San Diego, or something like that, and the computer sets it to 18:00:00 or 19:00:00 something like that depending on the daylight savings setting and the base offset. I'm not sure what the actual offset is for San Diego btw, that was just an example.

Then, if Joe in Chicago pulls it out, he'll get 14:00:00 or 15:00:00 or 13:00:00 or whatever based on the offset and his setting for time zone.

1. as i mentioned, i'm allowing users to set their own timezones. on every page request i can simply set the timezone setting but i still don't see how that will affect any of the GMT dates/times when they are pulled out of the db.

Remember, time zone setting affects the displayed, not stored, time. Well, it affects the stored time in terms of absolute setting when you set it the first time, but it's converted to GMT internally (or at least it should be) and converted back to the client's local timezone on retrieval. If you don't want this, then you'll need to make a separate field to store the timezone and use that to set it on each retrieval. you can usually use a cast of some sort to cast and interval multiplication to get the retrieved time to the time zone you want. Another reason to abandon the dodgy MySQL 3, it has no casts, what database has no casts???

2. i still don't see how any of these timezone features in mySQL will solve my problem with the DST adjustment. as i said before, manually shifting all of the dates to a specfic timezone is the easy part. how do i know which dates are in the DST window or not.

Again, are you thinking the timestamps are stored WITH their time zone? In standard SQL they're not. if you want the time zone stored you get to do it, and you get to pump the GMT dates through some kind of GMT to proper timezone conversion on each retrieval.

3. it appears that all of that timezone stuff was added in mySQL 4.1.3 and i was hoping for a lower minimum system requirement with this app so i guess i would still have to do the date math manually anyway.

Your choice. I gave up on MySQL 3 (and even 4 actually) a year or so ago. 5.0.19 is quite stable, and works well enough for this kind of stuff. My mainline development is still in PostgreSQL, but I don't use a 5 or 7 year old version of that either.

i'm begining to think that i will just have to map out the future DST shifts in a table and look it up for each date.

You can probably use the table MySQL supplies for upgrades to 4.0.3 and above in 3.xx.yy and do the maths yourself.

i'm sure i'm not the first person to address this problem so i assume somewhere someone has a CSV or something with all of that data mapped out. even if it is mapped out it seems that it is certainly subject to change. George W Bush signed an energy bill last year that will change the DST shift in the US starting in 2007. i suppose anyone who is still using an old palm pilot or whatever will likely see the wrong date/time for part of next year.

Of course somebody's done it. It's been part and parcel of most linux and other unix distros for some time (barring HPUX and Windows, which are still apparently using the digital equivalent of stone knives and bear skins for time zone manipulation). And Version 4.0.x and up of MySQL support it. And PostgreSQL has for quite some time.

take a look here: [URL=ftp://elsie.nci.nih.gov/pub/]time zone data[/URL] and here: MySQL page on time zone data tables

Note that this will be putting one set of time zone data in the database, and another set in the OS, which might not agree, but you'll likely not be using the one in the OS if you're using 3.xx.yy since it can't access the OS time zones anyway.

Ya know, this is one of those little things that is so frustrating about the continuing use of MySQL 3.23.xx. It's out of date, it's missing many key functionalities. The next three versions of MySQL (4.0, 4.1, 5.0) have been released and bug tested. They are stable. they can do so much more. And yet, in large part because of the tactic of GPLing the connect libs, and also due to the intertia of "if it ain't broke don't fix it" mentality of hosting companies, you get to reinvent the wheel again and again.

The same thing, by the way, happens to a lesser extent to PostgreSQL. The latest version will have some huge improvement in it, and somebody shows up on the list asking why 7.4.xx doesn't do x, y and z. Well, 8.0 or 8.1 do it. upgrade. They can't, they're stuck on 7.4 due to management decisions or their hosting provider.

Good luck on this, hope I've pointed you in the right direction at least. Please keep us informed of how you've handled it and how it worked out, won't you?

[MarkR]

The best way of doing this is NOT having the users set their time zone GMT offset - this will change twice a year for many people.

Instead, let them choose a time zone from the PHP time zones.

Store all date/times in your database in GMT, then convert each time you display.

MySQL 4.1+ (I think) has a current_timestamp_gmt function (or something similar). You could use that, or time() with the appropriate formatting.

The main problem with storing the date a future events, is that they're generally scheduled in local time, and they might be the other side of DST. THis means you can't simply process a date/time input in the user's CURRENT timezone, you'd have to figure out when it is, based on the time zone that it will be THEN.

This also creates problems for events scheduled during the 1 hour per year which is ambiguous, although in practice, few events are actually scheduled then, as it's typically in the middle of the night.

I don't have a tidy solution for this, but what I'm mainly doing is:

• Store all dates/times in GMT
• Except for future event dates, which are stored in local time for the time zone the event will happen, whatever that happens to be at the time.

Sometimes political decisions influence the time zone in use in a particular country, that are inherently unpredictable (f.e. The Australians' decision to delay DST 1 week this year due to the Commonwealth games).

Mark

[Sxooter]

OK, Mark and I are saying the same thing from different directions.

When you give MySQL a timestamp, it assumes it's in the server's timezone unless you set the timezone with "SET time_zone = timezone;". That timezone argument can be a numeric offset (fixed) or a timezone from the system timezone database. If it is a timezone from the system database, MySQL will know whether to apply an offset of daylight savings time. Either way, the MySQL database will convert from the timezone set to GMT / Zulu and insert the GMT timestamp into the database. Here's a short example:

create table test (dt timestamp);
SET time_zone = 'GMT';
insert into test values ('2006-03-01 12:00:00');
select * from test;
2006-03-01 12:00:00
SET time_zone = 'America/Chicago';
select * from test;
2006-03-01 06:00:00
delete from test;
SET time_zone = 'GMT';
insert into test values ('2006-04-10 12:00:00');
select * from test;
2006-04-10 07:00:00

Notice the offset was different for each of these since one was in standard time, and the other was handled by daylight savings time.  notice too, that it was automatic.

Note that we can run the conversion the other way:

SET time_zone = 'America/Chicago';
insert into test values ('2006-04-10 12:00:00');
insert into test values ('2006-03-10 12:00:00');
select * from test;
2006-04-10 12:00:00
2006-03-10 12:00:00
SET time_zone = 'GMT';
select * from test;
2006-04-10 17:00:00
2006-03-10 18:00:00

Note that offsets now move in the other direction, and they adjust for DST settings as well.

so, you can code this up all by yourself, or you can let MySQL do the job for ya.

[Sxooter]

Please note that, as I so often do, I have to give a nod to PostgreSQL for implementing this sooner and better.

With MySQL I had to run a special script to import the timezone database into MySQL, which means that at some point in the future when those data are updated, I'll have to pay attention again. Making this worse is the typical way the MySQL docs send you to another page, and yet another page to find the little usility program to run to make this stuff work. It feels bolted on.

PostgreSQL, as per usual methods, hits the internal timezone database directly and just knows what the time zones are when you enter them, not muss no fuss.

Part of this difference lies in the fact that PostgreSQL is so natively unix. In the case of Windows, the solution often given is to talk to Billy boy about fixing his always broken OS (Even XP SP2 apparently doesn't have accurate time zone data in it) or use unix.

Meanwhile, MySQL, needing to work right in both environments since they sell commercial support for both, are forced to bolt on something that can work in either.

So, if you're a unix user, the unix (mostly) database wins. If you're a windows user, mysql wins.

[devinemke]

Sxooter and MarkR, thank you both. i really appreciate this info.

as i said, this app i'm designing is not just for me on my own server but it will distributed and i obviously can't know the specifics of the user's os/server. it appears from the mySQL manual pages that Sxooter refered to that the timezone data does not come "out of the box" with the main mySQL package (at least not the windows version). you must download a separate set of table definitions and load them up to refer to timezones by name. because of this i cannot rely upon those tables being there and i suppose i will have to maintain my own set of timezone tables as part of my own distribution. if i do that, all of those adjustments will not happen automagically, i must do the math myself from within my app.

Sxooter i understand and appreciate many of the shortcomings of mySQL compared to other popular RDBS's however i have chosen to base this app on mySQL as i have found it to the most common one on commercial hosts. it also seems to be the only RDBMS installed on the servers of many of the specific people who have expressed interest in using this app that i am designing.

[Sxooter]

WIth MySQL 4.0 and up, I think you just need to be able to execute that script (which you can in a php system that's not running in safe mode) so you don't have to download and install separate tables.

OTOH, windows is hosed by nature, so there, yeah, you might not be able to expect proper behaviour.

RE: The postgresql versus MySQL portion of my post. I totall get what you're saying about availability and the lowest common denominator. It's just kinda sad that even people who prefer MySQL are stuck with ancient versions with known issues because the hosting companies are still running the creaky old 3.x code base.

[devinemke]

Sxooter wrote:

WIth MySQL 4.0 and up, I think you just need to be able to execute that script (which you can in a php system that's not running in safe mode) so you don't have to download and install separate tables.

actually i don't think that is correct. i did a little testing (WinXP/mySQL 4.1.7) and here is what i came up with:

as predicted, on a windows machine unless and until you setup the timezone tables in your "mysql" database you can only specify the timezone offset using a numer (like "-8:00") and that offset will of course be absolute (meaning it will be that offset regardless of the DST shift). once you install the timezone tables (available here) you can then specify the timezone by name (like "US/Eastern") and mysql uses those tables to adjust the GMT times and apply the DST shift.

mysql_query('DROP TABLE IF EXISTS test') or exit(mysql_error());
mysql_query('CREATE TABLE test (dt DATETIME)') or exit(mysql_error());

mysql_query("SET time_zone = '+0:00'") or exit(mysql_error());
mysql_query("INSERT INTO test VALUES (NOW())") or exit(mysql_error());
$result = mysql_query('SELECT * FROM test') or exit(mysql_error());
echo mysql_result($result, 0); // outputs current GMT time

echo '<br>';

mysql_query("SET time_zone = '-5:00'") or exit(mysql_error());
$result = mysql_query('SELECT * FROM test') or exit(mysql_error());
echo mysql_result($result, 0); // outputs current EST time (no DST shift)

echo '<br>';

mysql_query('DELETE FROM test') or exit(mysql_error());

mysql_query("SET time_zone = 'GMT'") or exit(mysql_error());
mysql_query("INSERT INTO test VALUES (NOW())") or exit(mysql_error());
$result = mysql_query('SELECT * FROM test') or exit(mysql_error());
echo mysql_result($result, 0); // outputs current GMT time

echo '<br>';

mysql_query("SET time_zone = 'US/Eastern'") or exit(mysql_error());
$result = mysql_query('SELECT * FROM test') or exit(mysql_error());
echo mysql_result($result, 0); // outputs current EDT time (with DST shift)

unfortunately, this all only seems to apply if you are using the TIMESTAMP data type. if you are using the DATETIME type (which i was planning on using) the conversion is not done. i have no idea why this would be. would it not seem logical that mySQL would apply the timezone/DST conversion to any/all temporal data types? it's also a real drag because i specifically did not want to use the TIMESTAMP type because i do not want/need the auto updating of the time on every UPDATE query (i realize that there are ways around this).

at any rate, it still seems that mySQL running on windows using the main distribution package does not allow me to easily shift all of my dates for TZ/DST.

[Sxooter]

Glad to see it works. the simplest work around for the auto-updated timestamp type is to just declare a dummy field first. MySQL only updates the first timestamp field automagically.

Choosing timestamp type as the autoupdated and not datetime was a very stupid move that they got stuck with after implementing it. If you look in the SQL spec, there is no datetime type, but there is a timestamp type. So, if someone had been paying ANY attention to the SPEC back in the day, and swapped it around we'd all be fat and happy now. Ah well, looks like they're slowly but surely complying with the spec (and breaking legacy apps in the process) nowadays.

Hope you can get it to work.

[MarkR]

What I don't know, is any convenient way of finding out when a given time will be in the future (or past).

So for example, how can I ask "When is 7.30 PM on the 1st of October 2006"?

Bearing in mind that this may not be in the same time offset as I am at the moment, because of daylight saving time (idiotic system that it is).

Also, events which happened in the past, happened at a distinct moment in time. However, when my users look at the reports for this historical data, they appear to move backwards and forwards 1 hour with DST. This is because I'm outputting dates in the current time zone, which is great, but things appear to move around.

I'm saying, I don't have any way of formatting a date/time in the time offset which was (or will be) active at that time.

Mark

[Sxooter]

All of those data are stored in the tomezone database. Note that it gets updated over time, but since it can't tell the future, it may well be guessing about future dates. For the past, it should get them right.

So, when you tell the machine your timeone is "Australia/Sidney" the database knows if DST was in effect or not for a particular date. It's the numeric offsets that can't adjust, like -08:00. So don't use those. but the named ones DO know if a date was in Daylight time or not. try it, you'll see

[devinemke]

with regard to my question about why mySQL would only have the TIMESTAMP follow timezone settings and not DATETIME, i found this in the manual here:

...data values of the TIMESTAMP data type are automatically interpreted as values belonging to the connection's current time zone. TIMESTAMP values thus behave the way they do in Oracle's TIMESTAMP WITH LOCAL TIME ZONE data type — that is, values stored in a TIMESTAMP column are normalized towards UTC and converted back to the current connection time zone at SELECT time. This gives MySQL the ability to provide the "current" TIMESTAMP value, even if the database is moved to a different location.

that page goes on to explain that the CONVERT_TZ() function will work as expected on any temporal data type. CONVERT_TZ() would seem to be handy if you don't want to use the TIMESTAMP type but it still is a pain that the global timezone setting will have no affect on any type other than TIMESTAMP.

[Sxooter]

It would have been nice if they'd one so that you could add "with timezone" to either type and gotten tz behaviour, and left the ones with it to be absolute t free timestamps, which is what the spec does. Note that pgsql used to do timestamp the same way (ie only with timezone.) When they changed it (around v 7.1) it broke plenty of apps that relied on the old behaviour, but the fix on the app side is pretty simple really, just change the type as declared in the db schema

[devinemke]

i realize this may be a dead horse but i thought i'd give it one more whack.

the basic solution i ended up with is to not use any of the named timezones (i can't rely upon that data being available). instead i use numeric offsets. i changed my minimum requirement to mySQL 4.1.x or higher (my app runs a quick version check on each page request and halts if the version is too old). i changed all of my DATETIME fileds to TIMESTAMP fields and set them not to auto update. in a common included file that is called at the top of every page request i do the following:

mysql_query("SET time_zone = '+0:00'");

the timezone is set like this (GMT) for every part of the app where a new record is created. i use the following to construct my date stings for new records:

$timestamp = time();
$gm_date_time = gmdate('Y-m-d H:i:s', $timestamp);

i feed this $gm_date_time to all of my INSERT queries. later down the page request where records are displayed i do the following:

$timezone = $config['timezone'];
if ($config['dst']) {$timezone += 1;}
if ($timezone >= 0) {$timezone = '+' . $timezone;}
if (strpos($timezone, '.5') !== false) {$timezone = str_replace('.5', ':30', $timezone);} else {$timezone .= ':00';}

this essentially reformats the $config['timezone'] (which is a just a positive or negative integer) to a string that can be fed to a new time_zone query (like "-8:00" or "+4:30"). i also applies the DST shift if the user has that boolean field set to ON

mysql_query("SET time_zone = '$timezone'");

this all works as expected. the one thing that tripped me up is that there are various places around the app where a user can edit records (via a popup form). when the form is rendered the fields are pre-populated with all of the existing data. this of course means that the pre-populated date/time is the adjusted one for the user's timezone. when the form is submitted i of course have to run the SET time_zone query again to change back to the user's timezone before the UPDATE or else the date/time will be updated incorrectly (essentially double shifted by the timezone offset). before i figured this out i was testing and all of the date/times were progressively shifting back and back again on each update. this took me a little while to get my head around.

obviously this is not a perfect solution since users will have to check/uncheck the boolean DST config field twice a year (if they live in a DST locale). i realize that i could do away with the DST boolean alltogether and just tell users to change their timezone twice a year but i wanted to make this a bit more intuative. having to reset a system twice a year (which many of us have to do anyway with our wrist watches) i believe is a worthwhile trade off in the interest of portablility.

i hope this helps someone else and as always i am open to suggestions for a more elegent solution.

thanks.

[Sxooter]

I certainly don't feel you're beating a dead horse. This is one of those subjects that too few people are really familiar with. It pains me every time I see someone saying they do all their date maths in php, cause once you start crossing time zones, the date maths get more and more complicated to do, and the database is already made to do that. This topic should be made sticky...

[devinemke]

shortly after my last post i did some more testing and realized something quite obvious that makes my previous solution not workable. applying the DST shift as i did will shift all dates ahead 1 hour globally, including ones that are not within the DST window. this means that if you had DST turned ON and were looking at an old record from January (which is not in DST) it would be displayed 1 hour ahead of the actual time the record was created. this is a problem.

after considering all of my options here i think i have no choice but to do all of the date calculations in PHP. i realize that this is a no-no, and believe me that in many other apps that i have worked on (that do not need user specifed timezones) i always do this type of calculating and formatting using the RDBMS's native date functions. i just cannot see how i can reliably and portably do that in this case.

anyway here was i have (for now):

as before, i set the timezone to GMT at the top of each page request:

mysql_query("SET time_zone = '+0:00'"); 

this will be globally set for all INSERT/UPDATE/SELECT queries. all date strings are inserted with current dates formatted with [man]gmdate[/man]. i changed all of my date fileds back to DATETIME types (rather than TIMESTAMP). this has two benefits: 1. i never have to worry about auto-updates of the date 2. the dates will always be displayed as GMT dates in my mySQL GUI admin tool (if they were TIMESTAMPS they would all be shifted to my server's timezone). then, i do my SELECT queries like this:

mysql_query('SELECT *, UNIX_TIMESTAMP(date_time) AS timestamp FROM table');

then all of those unix timestamps are run through the following function:

function timezone_adjust($timestamp)
{
			global $config;

		$timestamp += $config['timezone'] * 3600;
		if ($config['dst'] && date('I', $timestamp) == 1) {$timestamp += 3600;}

		return $timestamp;
}

this shifts the timestamp to the user's timezone and also tacks on an extra hour if the user has set the DST preference to ON and the date in question is within DST or not. finally the date is formatted to be human readable. a typical usage might look like this:

mysql_query('SELECT *, UNIX_TIMESTAMP(date_time) AS timestamp FROM table');
while ($row = mysql_fetch_assoc($result))
{
	$row['date'] = gmdate('Y-m-d H:i:s', timezone_adjust($row['timestamp']));
	// rest of display code here
}

then, as i mentioned before i have my edit record popup form which get pre-populated with the shifted date. when the form is submitted i do the following:

$current_timestamp = time();
$timezone_diff_seconds = $current_timestamp - timezone_adjust($current_timestamp);
$timestamp_from_form = strtotime($date_from_form);
$date_time_for_update = date('Y-m-d H:i:s', $timestamp_from_form + $timezone_diff_seconds);

this pulls the submitted date back to GMT for insertion simply be adding back the number of seconds difference.

the only pitfall i see here is that i am determining whether the date fed to my timezone_adjust() function is within DST or not by using the "I" argument to the [man]date[/man] function. the PHP manual is not clear on this but what internation standard does [man]date[/man] use to make that determination? the US standard? is [man]date[/man] smart enough to use the server's clock to decide that? what if the server is in a timezone that does not recognize DST? will [man]date[/man] then always return false? i did a test on my windows machine by turning DST off in my control panel and then running this:

echo date('I');

even with DST off this still returned "1" meaning that the current date is within DST (which it is, 2006-04-05). i do not know if this would be the same behavior on all other platforms (unix?). i hope so because that would mean it could be used for this purpose on any server regardless of what timezone/DST recognition locale. this revised method also has the added benefit of not making users turn DST on/off twice a year in my app.

as always, i hope this is helpful to someone else.

[Sxooter]

Wow. The hoops folks have to jump through to make things work sometimes.

Meanwhile, folks with timezone support in their database are just setting the timezone...

I feel your pain.

[photo312]

One improvement to your script would be to set up a variable for mysql so that at the time it starts it automatically makes the time zone as GMT or UTC. This way you would avoid having to set the time_zone with mysql each time.

You should also set the timezone with PHP:

date_default_timezone_set('GMT');

Here is a quick summary:

1) Always store the accurate date (in your time zone) within the
database. Do not try adding or subtracting hours to compensate for
MySQLs MST default setting. If you do this it can throw off queries
when you are searching by date.

2) UNIX_TIMESTAMP is always GMT. This is the heart of the problem since
the database is converting the dates (from whatever time zone MySQL is
set to) to GMT time. Unix timestamps are always the number of seconds
since 1970 GMT!
3) MySQL has its own time zone setting that can be configured. I added
the following lines to "my.cnf" I added this as root but each MySQL user
also has their own ".my.cnf" that can be used to override the global
settings. If you do not find "[mysqld_safe]" in brackets already in
my.cnf you may need to add it. The following time zone setting is PST
on my system, but it my be different on yours. On linux I found the

available time zones in the file /usr/share/zoneinfo/zone.tab

[mysqld_safe]

timezone = America/Los_Angeles

4) This is the case in PHP as well. The PHP function TIME() will
return a GMT... number of seconds since 1970. It is always GMT. When
you run the PHP function DATE() it will convert the Unix timestamp into
whatever time zone you are in. So make sure to set the time zone on
your PHP system. I added the following line to a constants file that
gets included on every PHP script on my system... but you can configure

it in php.ini as well.

putenv ('TZ=America/Los_Angeles');

5) You should also set the time zone correctly on your server. You can
check by typing "date" at the shell prompt and see what it says. This
is not super important for web development though because everything is
abtracted through Unix timestamps which are GMT. The basic rule...
each application needs its own time zone setting so it knows how to do
the conversions.

6) For really fancy applications where every user sees dates shown in
their own time zone (based on their login)... you should set the time
zone in MySQL to GMT and store the dates within the DB as GMT as well.

Then abstract a PHP function to modify dates before an insert, similar
to what this article mentions. It is very important that the time
zone setting in mysql is set to GMT if you go this route. Then modify
the time zone setting in the PHP environment based upon login.