MySQL DATETIME vs TIMESTAMP
TIMESTAMP : Very importan it stores the number of milliseconds that have been elapsed Since
1st January 1970 GMT.
1)When you are inserting a DATE value into the MYSQL TIMESTAMP
TIMESTAMP values are converted from the current time zone to UTC for storage,(i.e it will store the number of milliseconds elapsed from
1st January 1970) and converted back from UTC to the current time zone for retrieval.
(This occurs only for the TIMESTAMP data type, not for other types such as DATETIME.
2) Timestamp has some specific qualities (ie.)
The first timestamp field in the database is automatically intialized and update with the current time
on every update of the row.
For More details See Please see : http://dev.mysql.com/doc/refman/5.0/en/timestamp.html
3) Time stamp value is stored in 4 bytes
4) The range of values that this TIMESTAMP will store is very limited.(1970 to 2037)
DATETIME : 1) 8 bytes storage
2) Just stores what you have stored and retrieves the same thing which you have stored.
3) It has nothing to deal with the TIMEZONE and Conversion.
August 18, 2008 at 12:55 pm
So from data consistency perspective and considering migration issues, using datetime is better?
December 9, 2008 at 9:57 am
Yes prasanna
February 13, 2009 at 6:36 am
I assume you meant seconds since 1970. Storing milliseconds in a 4 byte field would give a limit of less than 50 days