To go along with @ypercube's comment that CURRENT_TIMESTAMP
is stored as UTC but retrieved as the current timezone, you can affect your server's timezone setting with the --default_time_zone option for retrieval. This allows your retrieval to always be in UTC.
By default, the option is 'SYSTEM' which is how your system time zone is set (which may or may not be UTC!):
mysql> SELECT @@global.time_zone, @@session.time_zone;
+--------------------+---------------------+
| @@global.time_zone | @@session.time_zone |
+--------------------+---------------------+
| SYSTEM | SYSTEM |
+--------------------+---------------------+
1 row in set (0.00 sec)
mysql> SELECT CURRENT_TIMESTAMP();
+---------------------+
| CURRENT_TIMESTAMP() |
+---------------------+
| 2012-09-25 16:28:45 |
+---------------------+
1 row in set (0.00 sec)
You can set this dynamically:
mysql> SET @@session.time_zone='+00:00';
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT @@global.time_zone, @@session.time_zone;
+--------------------+---------------------+
| @@global.time_zone | @@session.time_zone |
+--------------------+---------------------+
| SYSTEM | +00:00 |
+--------------------+---------------------+
1 row in set (0.00 sec)
Or permanently in your my.cnf:
[mysqld]
**other variables**
default_time_zone='+00:00'
Restart your server, and you will see the change:
mysql> SELECT @@global.time_zone, @@session.time_zone;
+--------------------+---------------------+
| @@global.time_zone | @@session.time_zone |
+--------------------+---------------------+
| +00:00 | +00:00 |
+--------------------+---------------------+
1 row in set (0.00 sec)
mysql> SELECT CURRENT_TIMESTAMP();
+---------------------+
| CURRENT_TIMESTAMP() |
+---------------------+
| 2012-09-25 20:27:50 |
+---------------------+
1 row in set (0.01 sec)
CURRENT_TIMESTAMP
? – ypercubeᵀᴹ Jul 1 '12 at 11:36MySQL converts TIMESTAMP values from the current time zone to UTC for storage, and back from UTC to the current time zone for retrieval.
– ypercubeᵀᴹ Jul 1 '12 at 12:05CURRENT_TIMESTAMP
). From the same documentation page:(This does not occur for other types such as DATETIME.)
. – AronVanAmmers Mar 2 '15 at 20:03