mysql GMT字符串转时间,MySQL查询当前GMT时间

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!

解决方案

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

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

or you use UTC (doesnt get affected with daylight savings time)

SELECT UTC_TIMESTAMP();