[mysql] How do I get the current time zone of MySQL?

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...

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 

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?

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 "&currentdate)
dbdate.Close()
Set dbdate = Nothing

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')

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).

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

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

If you need the GMT difference as an integer:

SELECT EXTRACT(HOUR FROM (TIMEDIFF(NOW(), UTC_TIMESTAMP))) AS `timezone`

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().

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 query below returns the timezone of the current session.

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

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.

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  |
+-------+

You can try the following:

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

Here you can specify your time difference as seconds

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.

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.