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.

Hi i had simmilar problem.

My server is in US and i'm from Poland.

Solution is quite simple:
In PHP after mysql_connect and mysql_select_db functions just execute:
SET time_zone = 'kN:00'
where
k = {+, -}
N = [1-24]
so for example SET time_zone = '+2:00'

after that NOW() will return proper time.

To set the MySQL timezone to UTC without privilege:

  SET SESSION time_zone = '+0:00'

to read the timezone which currently applies to NOW(), FROM_UNIXTIME(), 
UNIX_TIMESTAMP(), and the implicit conversion done when reading and 
writing columns of the TIMESTAMP type:

  SELECT @@session.time_zone

  do not use the magic timezone name 'SYSTEM' anywhere, because if you
have 
  used 'SET SESSION time_zone' then you are not operating under 'SYSTEM'

  timezone.  For example CONVERT_TZ(<time>, 'SYSTEM',
@@session.time_zone) 
  is no longer a no-op in that case.

to write a UNIX timestamp into a DATETIME column in UTC, regardless of
the 
session timezone:

  CONVERT_TZ(FROM_UNIXTIME(%ld), @@session.time_zone, '+0:00')     
(**note)

  to write the same to a TIMESTAMP column, instead of a DATETIME column:

  FROM_UNIXTIME(%ld)

to write the present time to a DATETIME column in UTC, regardless of the

session timezone:

  CONVERT_TZ(NOW(), @@session.time_zone, '+0:00')                  
(**note)
  
  to write the same to a TIMESTAMP column:

  NOW()

to read a UNIX timestamp out of a DATETIME column stored in UTC:

  UNIX_TIMESTAMP(CONVERT_TZ(column, '+0:00', @@session.time_zone)) 
(**note)

  to read a UNIX timestamp out of a TIMESTAMP column:

  UNIX_TIMESTAMP(column)

to read a date and time, in UTC, pretty-printed by MySQL, out of a
DATETIME 
column stored in UTC:

  SELECT column

  to read the same date out of a TIMESTAMP column:

  SET SESSION time_zone = '+0:00'
  SELECT column

  it is not possible to use CONVERT_TZ to read a TIMESTAMP column.  You 
  _have_ to set the session time_zone instead.  You'd think CONVERT_TZ 
  would work, but AFAICT passing @@session.time_zone as an argument to 
  CONVERT_TZ works on INSERT but does not work on SELECT.


summary:

  If you don't want to set the MySQL process's timezone to UTC (even
just 
  for the session), then this should be safe and correct:

   * use only TIMESTAMP columns

   * move all data in and out of the database exclusively using 
     FROM_UNIXTIME() and UNIX_TIMESTAMP() coercion


  If you care about leap seconds, and understand leap seconds and the
difference 
  between UTC, TAI, and GMT:

  
http://en.wikipedia.org/wiki/UNIX_time#Non-synchronous_Network_Time_Prot
ocol-based_variant

  and for some reason believe that MySQL will actually store leap
seconds in 
  a column, calculate intervals including leap seconds magically solving
the 
  impossiblity of doing this six months into the future, and you believe
NOW() 
  will sometimes return 23:59:60, and in spite of the lack of
documentation about 
  all this you believe the whole thing will cooperate with 
  your overall application to handle leap seconds (i.e., you never ask
the 
  kernel for the present time with a syscall instead you SELECT NOW(),
or if you 
  ask the kernel you ask with ntp_gettime() and pay attention to the
return code 
  not just the ntptimeval, and you have some magical library that takes
(returncode, 
  ntptimeval) and translates it into SQL time or user-presentable time,
and 
  you think it will work to roll your clock back to before a leap second
to test 
  this whole monster (I don't), and you are loading fresh leap second
data into 
  both MySQL and your Unix (returncode, ntptimeval) pretty-printing
library at 
  least twice a year, then:

   * you must set the session time_zone to UTC.

   * once you've done that, you must read/write the database 
     with SQL time representation, never using FROM_UNIXTIME() or 
     UNIX_TIMESTAMP().

   * you should probably use TIMESTAMP columns, but DATETIME columns 
     should also work if the time_zone stays UTC.  If you touch the 
     database from outside your app, for example you try to put NOW() 
     into such a column from phpMyAdmin with time_zone local, that will 
     be wrong if you used DATETIME and right if you use TIMESTAMP.


  If you want to use DATETIME columns without setting the session
time_zone,

   * change your mind.  There is no way to make a DATETIME column 
     unambiguously represent the hour between 1 and 2am each fall in 
     timezones that have summer time.  This is (**note).

   * If you choose to have a DATETIME column but store it as UTC 
     instead of storing local time like MySQL expects you to be doing, 
     then you must accomplish this using 'SET SESSION time_zone' to 
     avoid the summertime problem above.  If  you use CONVERT_TZ 
     anywhere to accomplish this goal, you will have the summer 
     time 1 - 2am ambiguity problem.
 
   * It is not an issue of ``using the same timezone everywhere.''  
     It is not merely a matter of the database being tied to a 
     particular local timezone without having that local timezone 
     stored inside the database, so problems arise from 
     the inelegance of the database on its own not fully representing 
     your data until you unlock it with the secret key---knowledge of 
     the local timezone.  And it's not a matter of avoiding messy 
     implicit conversions---passing local time on the interface between 
     application and MySQL when both app and MySQL are dealing 
     internally in UTC, which can go awry every time the timezone rules 
     change if the querying application and MySQL itself do not have 
     the same tzinfo dataset which is quite likely.  EVEN IF ALL THESE 
     THINGS WERE TAKEN CARE OF by some extremely meticulous person, you 
     would STILL have the summertime ``fall back'' issue above.  You 
     simply cannot use DATETIME at all.

   * Do not talk to me about leap seconds when there is one hour each 
     year that you cannot handle at all.