MySQL Set UTC time as default timestamp
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)
You can not specify UTC_TIMESTAMP
as default to specify automatic properties, You should use only the DEFAULT CURRENT_TIMESTAMP
and ON UPDATE CURRENT_TIMESTAMP
clauses.
Also you can INSERT UTC_TIMESTAMP
values like this though for a table:
CREATE TABLE `test` (
`ts` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);
INSERT query would be like this to insert UTC_TImeSTAMP:
insert into `test` (`ts`)
values
(utc_timestamp()) ;
My solution is with a trigger:
DELIMITER //
CREATE TRIGGER `update_to_utc` BEFORE INSERT ON `my_table` FOR EACH ROW BEGIN
set new.my_field=utc_timestamp();
END//
DELIMITER ;
Then every new inserted row will have the timestamp in UTC.