Timestamp data type looks like a date time

For a ‘date_added’ column, I thought it was always better to use a datetime data type instead of timestamp.

In MySql 5.0 I read there is no default if you use the datetime data type ( is that true? ). I want a default, so I switched to timestamp datatype. Which looks to be stored as a date time. I made 1 test record and the results of a default, in a timestamp data type is: 2013-08-24 13:16:12.

Does all of this sound correct? A timestamp data type saves this 2013-08-24 13:16:12, instead of the timestamp I expected i.e. 10982304982039482


The DATETIME type stores data in a readable format with no range limitations.

The TIMESTAMP type stores data in a UNIX timestamp, which has a limited range. However, it also converts the inserted timestamp to UTC before insertion, and automatically converts it to the server’s timezone when retrieved (something the DATETIME type does not do).

You can use DEFAULT on the column to specify a default date/time. If the column allows null values, it will default to null. Or, if the column value is not nullable, MySQL will automatically use the current date & time as the default value.


nope :slight_smile:


only for TIMESTAMP, not for DATETIME, and only for the first timestamp column in the tab;e

I slightly misspoke.

I can put a hard coded default in a datetime data type column, but mysql 5.0 has the option for dynamic ( if you will ) default if I use a timestamp data type.

I was just surprised to see the timestamp data type produce this:

2013-08-24 13:16:12

instead of this


I thought I did something wrong, or had missed a better solution.

did you happen to see how mysql stores DATETIME values?

hint: they are ~not~ stored as yyyy-mm-dd strings

similarly, timestamp values are not stored as yyyy-mm-dd hh:mm:ss strings

instead, they are stored as unix epoch integers, and when you select them, they are just displayed as yyyy-mm-dd hh:mm:ss strings

moral of the story: internal storage format is often quite different from displayed format