MySQL Database

ID #256

How do I change MySQL timezone?

Applies to: Grid System

The MySQL timezone is set to MST (-7 hours GMT/UTC) and is not configurable by you. MySQL is only capable of having 1 timezone setting per mysql daemon. Therefore, you cannot select NOW() and expect a result in a timezone other than MST.

However, there are ways for you to get results that are in your preferred timezone. First determine how many hours your desired timezone is off from MST. For example, EST is +2 hours. PST is -1 hour.

Knowing the time offset, you can replace all your SQL statements of

SELECT NOW();



with

SELECT DATE_ADD(NOW(), INTERVAL 2 HOUR);



which will give you an EST date result. For a result in PST, you would do:

SELECT DATE_SUB(NOW(), INTERVAL 1 HOUR);



If you are working with time in seconds instead of dates, then factor in the offset in seconds. Because there are 3600 seconds in an hour, and EST is 2 hours later than MST, the following converts timestamps from MST to EST:

SELECT unix_timestamp() + (3600 * 2);

 

SELECT FROM_UNIXTIME(UNIX_TIMESTAMP() + (3600 * 2));



See the MySQL Manual's Date and Time Functions for more information.

Depending on your application, you may also need to do one of the following (but not both):

1. Find every place in your code where a date or time is displayed to the browser and have a user defined function change it to add or subtract the appropriate number of hours before displaying it.

2. Find every place in your code where dates or times are input into your system and have a user defined function add or subtract the appropriate number of hours before storing it.

If you were looking for information about changing PHP's timezone, look here.



Last update: 2010-10-03 16:57
Author: FAQ Admin
Revision: 1.2

Digg it! Share on Facebook Print this record Send FAQ to a friend Show this as PDF file
Please rate this FAQ:

Average rating: 3.04 (26 Votes)

completely useless 1 2 3 4 5 most valuable

You can comment this FAQ

Comment of Anonymous:
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.
Added at: 2004-01-15 13:32

Comment of Anonymous:
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
Added at: 2004-03-07 15:07

Comment of Anonymous:
The unix_timestamp() example is NOT incorrect because the MySQL server time is NOT in GMT.
Added at: 2004-03-07 16:30

Comment of Anonymous:
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 MySQL’s 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.
Added at: 2004-03-19 15:05

Comment of Anonymous:
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.
Added at: 2005-09-26 13:27

Comment of Anonymous:
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.
Added at: 2006-02-23 12:15

Comment of Anonymous:
if using mysql >=4.1.1 you can use UTC_TIMESTAMP() and UTC_DATE() to get GMT times and dates.
Added at: 2006-05-29 01:37

Comment of Anonymous:
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.
Added at: 2008-07-18 05:06

Comment of Anonymous:
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(, '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_Protocol-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.

Added at: 2009-03-04 13:10

Comment of Anonymous:
See this bug: http://bugs.mysql.com/bug.php?id=50627

Added at: 2010-02-23 02:47