MySQL query for current GMT time

Question

This sounds simple enough but I haven't been able to figure out how to use a simple SELECT statement to return the current time in GMT.

I have been trying to use CONVERT_TZ() to convert NOW() to GMT based on the server time zone and the GMT time zone but for some reason it returns NULL when I put in the text time zones. The only way I get a result is to actually put in the offsets which is getting way too complicated for what should be a really simple operation. Here is what I mean:

mysql> SELECT CONVERT_TZ(NOW(),@@global.system_time_zone,'GMT');
NULL

mysql> SELECT CONVERT_TZ(NOW(),'PST','GMT');
NULL

mysql> SELECT CONVERT_TZ(NOW(),'-08:00','+00:00');
2010-02-13 18:28:22

All I need is a simple query to return the current time in GMT. Thanks in advance for your help!

Note: Use UTC time if you need a universal time reference. It's the real reference, not GMT. The latter changes when daylight saving is on. — culebrón, Feb 13 '10 at 16:49
@culebron - Thanks for the suggestion! The question still stands. How then can I use a simple select statement to get the current UTC time? — Russell C., Feb 13 '10 at 16:51

Rufinus · Accepted Answer · 2018-04-13T16:20:56.420

40

Just use UTC (doesnt get affected with daylight savings time)

SELECT UTC_TIMESTAMP();

Old Content for reference:

this should work, but with

SELECT CONVERT_TZ(NOW(),'PST','GMT');

i got also NULL as result. funny enough the example in the mysql docu also returns null

SELECT CONVERT_TZ('2004-01-01 12:00:00','GMT','MET');

http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html#function_convert-tz ~~seems you found a bug in mysql~~. (thanks to +Stephen Pritchard)

you could try:

SET @OLD_TIME_ZONE=@@TIME_ZONE;
SET TIME_ZONE='+00:00';
SELECT NOW();
SET TIME_ZONE=@OLD_TIME_ZONE;

ok is not exactly what you wanted (its 4 queries, but only one select :-)

edited Apr 13 '18 at 16:20

answered Feb 13 '10 at 16:58

Rufinus

29,200
6
68
84

3

@Rufinus - Good, I'm not crazy. How can MySQL have a bug in such an important function? The UTC_TIMESTAMP() was exactly what I was looking for. Thanks! – Russell C. Feb 13 '10 at 17:42
What if database saves records in UTC and search them using current timezone? at one time timezone in general was gmt -1 and after 6 months it becomes gmt +1 ?? – shzyincu Apr 13 '16 at 05:21
GMT doesn't get affected by daylight savings time neither. I think everybody here is looking for `UTC_TIMESTAMP()`, so it's a good idea to move your last line to the top of the answer. – Márton Tamás Apr 11 '18 at 09:19

score 13 · Answer 2 · answered Aug 18 '11 at 16:31

13

NO BUG in CONVERT_TZ()

To use CONVERT_TZ() you need to install the time-zone tables otherwise MySql returns NULL.

From the CLI run the following as root

# mysql_tzinfo_to_sql /usr/share/zoneinfo | mysql -u root mysql

SEE http://dev.mysql.com/doc/refman/5.5/en/time-zone-support.html

Thanks

http://www.ArcanaVision.com (SJP 2011-08-18)

answered Aug 18 '11 at 16:31

Stephen Pritchard

131
1
3

This worked for me almost everytime, but this time `convert_tz()` returns NULL even after timezone update. strange! and I checked - timezone_xxx tables are full. my server version is 5.0.92, compiled from source (as I always do). Never had this problem before... – NickSoft Feb 24 '12 at 09:34
3

Just found out that after filling the zones table `PST` time zone still does not exist. I read in wikipedia and It looks like that PST is not clear enough for defining a timezone because of daylight saving. Some of the cities use DST some do not. See: http://en.wikipedia.org/wiki/Pacific_Time_Zone#Daylight_time . I used Pacific/Los_Angeles rather than PST (to take DST in account). – NickSoft Feb 25 '12 at 17:31
1

I had to use 'US/Pacific' to get a non-NULL value from convert_tz() on my 5.5.27 database. I found this string by browsing through the mysql.time_zone_name table. – randalv Jan 09 '15 at 21:12

score 12 · Answer 3 · edited May 23 '17 at 12:25

12

Note: GMT might have DST UTC does not have DST

SELECT UTC_TIMESTAMP();

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

edited May 23 '17 at 12:25

Community

1
1

answered Oct 01 '13 at 09:12

Timo Huovinen

53,325
33
152
143

score 7 · Answer 4 · answered Nov 24 '15 at 20:53

7

The surefire way to fetch the current UTC datetime is:

SELECT CONVERT_TZ(NOW(), @@session.time_zone, '+0:00')

answered Nov 24 '15 at 20:53

Blue

22,608
7
62
92

score 0 · Answer 5 · answered Oct 13 '15 at 22:50

0

This should work - I think you are specified your timezone wrong. Using Chicago as example

SELECT CONVERT_TZ(NOW(), 'America/Chicago', 'GMT')

answered Oct 13 '15 at 22:50

konung

6,908
6
54
79

1

To use CONVERT_TZ() you need to install the time-zone tables otherwise MySql returns NULL ! They don't come preinstalled in mysql – konung Apr 14 '16 at 14:59

score 0 · Answer 6 · edited Sep 15 '16 at 16:09

When the source server timestamp is unknown but the requirement is for a specific tz do this:

select convert_tz(utc_timestamp(),'+05:00','+00:00')

This uses CST as an example. utc_timestamp will guarantee a '+00:00' result no matter where you query it. The '+05:00' is your desired offset. This works great when hitting multiple unknown servers and it assures you that you returning result will all be in a common tz result set.

score 0 · Answer 7 · answered Dec 26 '17 at 08:22

0

I was searching for my time zone which is GMT+6 (Asia/Dhaka TimeZone)

MySQL server is in US. The below worked for me.

SELECT CONVERT_TZ( UTC_TIMESTAMP( ) , '+06:00', '+00:00' )

answered Dec 26 '17 at 08:22

Zakaria Bin Abdur Rouf

1,195
1
6
6

score 0 · Answer 8 · answered Mar 16 '12 at 23:33

0

After seeing all the answers above and seeing it's unreliable to convert to PST, I just used this:

DATE_SUB(user_last_login, INTERVAL 7 hour)

answered Mar 16 '12 at 23:33

Mauvis Ledford

40,827
17
81
86

This doesn't account for DST rules. – Zambonilli Oct 31 '17 at 12:49

score -1 · Answer 9 · answered Jun 24 '16 at 14:13

-1

There is no bug in CONVERT_TZ function. You get NULL because you use time zones names/abbreviations. Mysql does not know what this 'GMT','PST', etc means unless you install mysql.time_zone_name table. However if you use numbers it will always work.

mysql> SELECT CONVERT_TZ(NOW(), 'America/Chicago', 'GMT');

returns NULL

mysql> SELECT CONVERT_TZ(NOW(),'-08:00','+00:00');

returns 2016-06-24 17:58:33

answered Jun 24 '16 at 14:13

Daniel

1

For some reason it needs a '+' or '-' in the 'from time zone' param. mariadb > SELECT CONVERT_TZ(NOW(),'-08:00','00:00'); returns NULL – klokop Nov 13 '18 at 11:43

MySQL query for current GMT time

9 Answers9

Linked