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.
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
10982304982039482
I thought I did something wrong, or had missed a better solution.