timezone – How do I get the current time zone of MySQL?

The Question :

237 people think this question is useful

Anyone knows if there is such a function in MySQL?

UPDATE

This doesn’t output any valid info:

mysql> SELECT @@global.time_zone, @@session.time_zone;
+--------------------+---------------------+
| @@global.time_zone | @@session.time_zone |
+--------------------+---------------------+
| SYSTEM             | SYSTEM              |
+--------------------+---------------------+

Or maybe MySQL itself can’t know exactly the time_zone used,that’s fine, we can involve PHP here, as long as I can get valid info not like SYSTEM

The Question Comments :
  • “we can involve PHP here” – and would that instance of php always be on the same machine as the MySQL server?
  • Yes,they’ll be on the same machine.
  • Try @@system_time_zone as stated in my answer below.
  • Since the question was asked more answers were added, maybe reconsider the accepted answer?
  • Even though the MySQL server and PHP are on same server, they may pickup different timezones based on their settings. And these times can differ from what your OS shows, and PHP/MySQL show.

The Answer 1

248 people think this answer is useful

From the manual (section 9.6):

The current values of the global and client-specific time zones can be retrieved like this:
mysql> SELECT @@global.time_zone, @@session.time_zone;

Edit The above returns SYSTEM if MySQL is set to slave to the system’s timezone, which is less than helpful. Since you’re using PHP, if the answer from MySQL is SYSTEM, you can then ask the system what timezone it’s using via date_default_timezone_get. (Of course, as VolkerK pointed out, PHP may be running on a different server, but as assumptions go, assuming the web server and the DB server it’s talking to are set to [if not actually in] the same timezone isn’t a huge leap.) But beware that (as with MySQL), you can set the timezone that PHP uses (date_default_timezone_set), which means it may report a different value than the OS is using. If you’re in control of the PHP code, you should know whether you’re doing that and be okay.

But the whole question of what timezone the MySQL server is using may be a tangent, because asking the server what timezone it’s in tells you absolutely nothing about the data in the database. Read on for details:

Further discussion:

If you’re in control of the server, of course you can ensure that the timezone is a known quantity. If you’re not in control of the server, you can set the timezone used by your connection like this:

set time_zone = '+00:00';

That sets the timezone to GMT, so that any further operations (like now()) will use GMT.

Note, though, that time and date values are not stored with timezone information in MySQL:

mysql> create table foo (tstamp datetime) Engine=MyISAM;
Query OK, 0 rows affected (0.06 sec)

mysql> insert into foo (tstamp) values (now());
Query OK, 1 row affected (0.00 sec)

mysql> set time_zone = '+01:00';
Query OK, 0 rows affected (0.00 sec)

mysql> select tstamp from foo;
+---------------------+
| tstamp              |
+---------------------+
| 2010-05-29 08:31:59 |
+---------------------+
1 row in set (0.00 sec)

mysql> set time_zone = '+02:00';
Query OK, 0 rows affected (0.00 sec)

mysql> select tstamp from foo;
+---------------------+
| tstamp              |
+---------------------+
| 2010-05-29 08:31:59 |      <== Note, no change!
+---------------------+
1 row in set (0.00 sec)

mysql> select now();
+---------------------+
| now()               |
+---------------------+
| 2010-05-29 10:32:32 |
+---------------------+
1 row in set (0.00 sec)

mysql> set time_zone = '+00:00';
Query OK, 0 rows affected (0.00 sec)

mysql> select now();
+---------------------+
| now()               |
+---------------------+
| 2010-05-29 08:32:38 |      <== Note, it changed!
+---------------------+
1 row in set (0.00 sec)

So knowing the timezone of the server is only important in terms of functions that get the time right now, such as now(), unix_timestamp(), etc.; it doesn’t tell you anything about what timezone the dates in the database data are using. You might choose to assume they were written using the server’s timezone, but that assumption may well be flawed. To know the timezone of any dates or times stored in the data, you have to ensure that they’re stored with timezone information or (as I do) ensure they’re always in GMT.

Why is assuming the data was written using the server’s timezone flawed? Well, for one thing, the data may have been written using a connection that set a different timezone. The database may have been moved from one server to another, where the servers were in different timezones (I ran into that when I inherited a database that had moved from Texas to California). But even if the data is written on the server, with its current time zone, it’s still ambiguous. Last year, in the United States, Daylight Savings Time was turned off at 2:00 a.m. on November 1st. Suppose my server is in California using the Pacific timezone and I have the value 2009-11-01 01:30:00 in the database. When was it? Was that 1:30 a.m. November 1st PDT, or 1:30 a.m. November 1st PST (an hour later)? You have absolutely no way of knowing. Moral: Always store dates/times in GMT (which doesn’t do DST) and convert to the desired timezone as/when necessary.

The Answer 2

206 people think this answer is useful

The query below returns the timezone of the current session.

select timediff(now(),convert_tz(now(),@@session.time_zone,'+00:00'));

The Answer 3

115 people think this answer is useful

Simply SELECT @@system_time_zone;

Returns PST (or whatever is relevant to your system).

If you’re trying to determine the session timezone you can use this query:
SELECT IF(@@session.time_zone = 'SYSTEM', @@system_time_zone, @@session.time_zone);

Which will return the session timezone if it differs from the system timezone.

The Answer 4

76 people think this answer is useful

As Jakub Vrána (The creator or Adminer and NotORM) mentions in the comments, to select the current timezone offset in TIME use:

SELECT TIMEDIFF(NOW(), UTC_TIMESTAMP);

It will return: 02:00:00 if your timezone is +2:00 for that date

I made a cheatsheet here: Should MySQL have its timezone set to UTC?

The Answer 5

12 people think this answer is useful

To get Current timezone of the mysql you can do following things:

 SELECT @@system_time_zone;   # from this you can get the system timezone 
 SELECT IF(@@session.time_zone = 'SYSTEM', @@system_time_zone, @@session.time_zone) # This will give you time zone if system timezone is different from global timezone

Now if you want to change the mysql timezone then:

 SET GLOBAL time_zone = '+00:00';   # this will set mysql timezone in UTC
 SET @@session.time_zone = "+00:00";  # by this you can chnage the timezone only for your particular session 

The Answer 6

10 people think this answer is useful

To anyone come to find timezone of mysql db.

With this query you can get current timezone :

mysql> SELECT @@system_time_zone as tz;
+-------+
|  tz   |
+-------+
|  CET  |
+-------+


The Answer 7

6 people think this answer is useful
SELECT EXTRACT(HOUR FROM (TIMEDIFF(NOW(), UTC_TIMESTAMP))) AS `timezone`

This will return the timezone as an integer (eg: -6), handling positive or negative times (here is where EXTRACT comes into play: HOUR function alone returns negative timezones as positive).

The Answer 8

4 people think this answer is useful

The command mention in the description returns “SYSTEM” which indicated it takes the timezone of the server. Which is not useful for our query.

Following query will help to understand the timezone

SELECT TIMEDIFF(NOW(), UTC_TIMESTAMP) as GMT_TIME_DIFF;

Above query will give you the time interval with respect to Coordinated Universal Time(UTC). So you can easily analyze the timezone. if the database time zone is IST the output will be 5:30

UTC_TIMESTAMP

In MySQL, the UTC_TIMESTAMP returns the current UTC date and time as a value in ‘YYYY-MM-DD HH:MM:SS’ or YYYYMMDDHHMMSS.uuuuuu format depending on the usage of the function i.e. in a string or numeric context.

NOW()

NOW() function. MySQL NOW() returns the value of current date and time in ‘YYYY-MM-DD HH:MM:SS’ format or YYYYMMDDHHMMSS.uuuuuu format depending on the context (numeric or string) of the function. CURRENT_TIMESTAMP, CURRENT_TIMESTAMP(), LOCALTIME, LOCALTIME(), LOCALTIMESTAMP, LOCALTIMESTAMP() are synonyms of NOW().

The Answer 9

3 people think this answer is useful

Check out MySQL Server Time Zone Support and the system_time_zone system variable. Does that help?

The Answer 10

1 people think this answer is useful

My PHP framework uses

SET LOCAL time_zone='Whatever'

on after connect, where ‘Whatever’ == date_default_timezone_get()

Not my solution, but this ensures SYSTEM timezone of MySQL server is always the same as PHP’s one

So, yes, PHP is strongly envolved and can affect it

The Answer 11

1 people think this answer is useful

To get the current time according to your timezone, you can use the following (in my case its ‘+5:30’)

select DATE_FORMAT(convert_tz(now(),@@session.time_zone,’+05:30′) ,’%Y-%m-%d’)

The Answer 12

1 people think this answer is useful

You can try the following:

select sec_to_time(TIME_TO_SEC( curtime()) + 48000);

Here you can specify your time difference as seconds

The Answer 13

0 people think this answer is useful

You just need to restart mysqld after altering timezone of System..

The Global time zone of MySQL takes timezone of System. When you change any such attribute of system, you just need a restart of Mysqld.

The Answer 14

0 people think this answer is useful

Insert a dummy record into one of your databases that has a timestamp Select that record and get value of timestamp. Delete that record. Gets for sure the timezone that the server is using to write data and ignores PHP timezones.

The Answer 15

0 people think this answer is useful

Use LPAD(TIME_FORMAT(TIMEDIFF(NOW(), UTC_TIMESTAMP),’%H:%i’),6,’+') to get a value in MySQL’s timezone format that you can conveniently use with CONVERT_TZ(). Note that the timezone offset you get is only valid at the moment in time where the expression is evaluated since the offset may change over time if you have daylight savings time. Yet the expression is useful together with NOW() to store the offset with the local time, which disambiguates what NOW() yields. (In DST timezones, NOW() jumps back one hour once a year, thus has some duplicate values for distinct points in time).

The Answer 16

0 people think this answer is useful

It may be

select timediff(current_time(),utc_time())

You won’t get the timezone value directly this way.

@@global.time_zone cannot be used as it is a variable, and it returns the value 'SYSTEM'.

If you need to use your query in a session with a changed timezone by using session SET TIME_ZONE =, then you will get that with @@session.time_zone. If you query @@global.time_zone, then you get 'SYSTEM'.

If you try datediff, date_sub, or timediff with now() and utc_time(), then you’ll probably run into conversion issues.

But the things suggested above will probably work at least with some server versions. My version is 5.5.43-37 and is a hosted solution.

The Answer 17

-3 people think this answer is useful

Try using the following code:

//ASP CLASSIC
Set dbdate = Server.CreateObject("ADODB.Recordset")
dbdate.ActiveConnection = MM_connection
dbdate.Source = "SELECT NOW() AS currentserverdate "
dbdate.Open()
currentdate = dbdate.Fields("currentserverdate").Value
response.Write("Server Time is "&amp;currentdate)
dbdate.Close()
Set dbdate = Nothing

Add a Comment