How to save records in MYSQL db with CURRENT_TIMESTAMP in UTC format and NOT LOCALTIME?

Viewed 161

Save records with column as 'created_date' which has CURRENT_TIMESTAMP which should be UTC time and not local time

2 Answers

UTC_TIMESTAMP() function

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.

Note : Since UTC_TIMESTAMP() works on current datetime, your output may vary from the output shown.

Syntax: UTC_TIMESTAMP; UTC_TIMESTAMP()

Code: SELECT UTC_TIMESTAMP,UTC_TIMESTAMP();

Sample Output:

 mysql> SELECT UTC_TIMESTAMP,UTC_TIMESTAMP();
+---------------------+---------------------+
| UTC_TIMESTAMP       | UTC_TIMESTAMP()     |
+---------------------+---------------------+
| 2015-04-14 22:52:11 | 2015-04-14 22:52:11 | 
+---------------------+---------------------+
1 row in set (0.01 sec)

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 ;
Related