45

How do I set a timestamp column whose default value is the current UTC time?

MySQL uses UTC_TIMESTAMP() function for UTC timestamp:

mysql> SELECT UTC_TIMESTAMP();
+---------------------+
| UTC_TIMESTAMP()     |
+---------------------+
| 2012-07-01 11:36:35 |
+---------------------+
1 row in set (0.00 sec)

So I've tried:

CREATE TABLE `blah` (
`creation_time` TIMESTAMP DEFAULT UTC_TIMESTAMP,
...

And other variations, like UTC_TIMESTAMP(), but without success.

Adam Matan
  • 10,129
  • 27
  • 75
  • 94
  • Did you try `CURRENT_TIMESTAMP` ? – ypercubeᵀᴹ Jul 01 '12 at 11:36
  • 3
    It works, but it does not store the a local timezone value instead of UTC. – Adam Matan Jul 01 '12 at 11:40
  • What exactly do you want to do? Store in the table column the UTC timestamp? Or something else? – ypercubeᵀᴹ Jul 01 '12 at 11:48
  • I want to store the current timestamp, in UTC, for every insertion (there are no updates). – Adam Matan Jul 01 '12 at 11:59
  • 10
    From [The DATE, DATETIME, and TIMESTAMP Types](http://dev.mysql.com/doc/refman/5.5/en/datetime.html): `MySQL 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 01 '12 at 12:05
  • 4
    Be aware that the storage as UTC only applies to the TIMESTAMP datatype, not to DATE and DATETIME (although you can set their default to `CURRENT_TIMESTAMP`). From the same documentation page: `(This does not occur for other types such as DATETIME.)`. – AronVanAmmers Mar 02 '15 at 20:03

6 Answers6

58

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)
Derek Downey
  • 22,870
  • 11
  • 76
  • 103
9

With MySql 8.0.13, you can use UTC_TIMESTAMP as the dafault value if you suround it with parentheses like so...

CREATE TABLE `blah` (
    id int NOT NULL AUTO_INCREMENT,
    creation_time TIMESTAMP NOT NULL DEFAULT (UTC_TIMESTAMP)
)
John C
  • 196
  • 1
  • 5
  • Yes but it will not work if you add ON UPDATE like this: `creation_time TIMESTAMP NOT NULL DEFAULT (UTC_TIMESTAMP) ON UPDATE (UTC_TIMESTAMP)` here https://bugs.mysql.com/bug.php?id=103228 someone raised an issue for it. Lets hope they will solve it – gshock Sep 12 '21 at 14:46
  • @GeorgiStaykov that's my issue report I raised ;-) – John C Sep 13 '21 at 03:36
6

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()) ;
Mahesh Patil
  • 2,946
  • 14
  • 22
4

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.

Alqin
  • 149
  • 1
  • 1
1

for mariadb only the global my.cnf solutions did work

for mariadb 10.2, the permanent solution of @Derek Downey in this post.

[mysqld]
**other variables**
default_time_zone='+00:00'

for mariadb 10.0 (i had 10.0.32), see https://stackoverflow.com/questions/947299/how-do-i-make-mysqls-now-and-curdate-functions-use-utc

[mysqld_safe]
**other variables**
timezone = UTC

both definitions may coexist in my.cnf of mariadb 10.2, but i don't have mariadb 10.0 anymore.

hope this will help you.

alex
  • 111
  • 2
1

John C's solution worked for me with

`Created` datetime NOT NULL DEFAULT (UTC_TIMESTAMP)

but I also had to change the sql modes because when I tried to add an index to this table I would get the error "ERROR 1067 (42000): Invalid default value"

removing the modes NO_ZERO_IN_DATE,NO_ZERO_DATE

resolved the issue with setting up the indexes.

set global sql_mode = "ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION"
Craig
  • 11
  • 2
  • They fixed that error in 8.0.24, I reported it in 8.022. https://bugs.mysql.com/bug.php?id=101486 – John C Sep 13 '21 at 03:38