Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
    
Did you try CURRENT_TIMESTAMP ? –  ypercube Jul 1 '12 at 11:36
    
It works, but it does not store the a local timezone value instead of UTC. –  Adam Matan Jul 1 '12 at 11:40
    
What exactly do you want to do? Store in the table column the UTC timestamp? Or something else? –  ypercube Jul 1 '12 at 11:48
    
I want to store the current timestamp, in UTC, for every insertion (there are no updates). –  Adam Matan Jul 1 '12 at 11:59
4  
From The DATE, DATETIME, and TIMESTAMP Types: 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 1 '12 at 12:05

2 Answers 2

up vote 13 down vote accepted

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)
share|improve this answer
1  
Very helpful post!! –  om39a Jun 2 at 19:44
    
such a clear and brief explanation.Thanks –  whokares Sep 9 at 14:04

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()) ;
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.