Customer Support

If MySQL had a user definable environmental variable for timezone so 
that you could issue a SQL query like: "SET SESSION timezone=EST" then
that would solve mysql timezone problems among different users. As of
4.x though, this does not exist.

the unix_timestamp() example is incorrect, since the unix timestamp
should *always* be GMT (seconds since '1970-01-01 00:00:00' GMT).

http://www.mysql.com/documentation/mysql/bychapter/manual_Functions.html
#IDX1363

The unix_timestamp() example is NOT incorrect because the MySQL server
time is NOT in GMT.

There seems to be a lot of confusion about this, even this article is
not correct.  I hope this will clear things up for some of you.

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. 

UNIX_TIMESTAMP() will return the seconds since 1970-01-01 00:00:00 GMT. 
However, UNIX_TIMESTAMP('1970-01-01 00:00:00') will assume the datetime
is in local time, not GMT.  I.e., for EDT servers, it will report it as
18000 seconds.

The previous note is correct: UNIX_TIMESTAMP assumes that the value in a
DATETIME field is local time - that is, the time set by the "SYSTEM"
time variable. However, here's a workaround I've found:

SELECT UNIX_TIMESTAMP (
CONVERT_TZ (my_datetime_column, '+0:00', 'SYSTEM'));

CONVERT_TZ converts the value of "my_datetime_column" to the system's
local time; UNIX_TIMESTAMP converts it back to Greenwich time, and
returns the time in UNIX epoch time.

This is a little convoluted, but it works.

if using mysql >=4.1.1 you can use UTC_TIMESTAMP() and UTC_DATE() to get
GMT times and dates.