MySQL : Timestamp
timestamp, data types, database
The TIMESTAMP datatype in MySQL has some nice features which aren't well known. It's a datatype used to store a date-and-time value.
Display and insert happens in the same way as the DATETIME datatype, the ISO 6801 date format (YYYY-MM-DD hh:mm:ss).
The date/time range
The range for a TIMESTAMP is different than for a DATETIME datatype:
DATETIME: '1000-01-01 00:00:00' -> '9999-12-31 23:59:59' TIMESTAMP: '1970-01-01 00:00:00' -> '2038-01-09 03:14:07'
Timezone
The TIMESTAMP is stored internally as UTC. When you insert a timestamp, it is converted to UTC using the system's timezone. When you retrieve it, it is converted back to the system's timezone.
As a sidenote, if you want to change the timezone for your session, you can do it easily with following SQL commands:
mysql> SET time_zone = '+01:00'; mysql> SET time_zone = 'Europe/Berlin';
If you want to use named timezones, be sure to have time zones set up in your mysql database (for more info, see the mysql timezone documentation).
Special features: auto values
Now, where it gets really interesting is the auto values:
Initialization & update values
One timestamp field in the table, can have the current date/time in the field on either insert and update, or both.
For INSERT you use the attribute "DEFAULT CURRENT_TIMESTAMP" and for UPDATE you use the attribute "ON UPDATE CURRENT_TIMESTAMP".
Examples:
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP updated_at TIMESTAMP ON UPDATE CURRENT_TIMESTAMP changed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
The important thing to remember is that only one timestamp field in the table can have one or more attributes. So if you want 2 fields with default behaviour, one for inserts and one for updates, you have to do it in another way... You use the attributes for the field you use to store the timestamp for updates, for the created timestamp look at the next part.
Default timestamp on null
If you specify that a timestamp can not be null, it will default automatically to the current date/time if you send a null value. So for a created_at field, you could just say:
created_at TIMESTAMP default 0
When you insert, you explicitely provide a null as value:
INSERT INTO mytable (created_at, ...) VALUES (null, ...);
So, I hope this is usefull to you and opens up some extra possibilities...
Have fun!
+32 475 62.42.64
kimpecov
2009-06-18 17:39