Saturday, June 5, 2010

Diff between MYSQL DATETIME and TIMESTAMP

Main advantages of DATETIME (DT) compared to TIMESTAMP (TS) :-
DT is human readable (TS is not without using TO_DATE)
DT has a possible timespan of 8999 Years (1000-01-01 00:00:00 to 9999-12-31 23:59:59)
(TS only about 68 years, 1970-01-01 to 2038-01-19)
DT can be used for advanced date calculation (SELECT NOW() + INTERVAL 2 DAY)
Not sure ( DT is faster than TS) 
http://dbscience.blogspot.com/2008/08/can-timestamp-be-slower-than-datetime.html


Main advantages of TIMESTAMP (TS) compared to  DATETIME (DT) :-
TS only needs 4 bytes (DT uses 8)
TS are stored as UTC values and changed according to the client's timezone setting
TS columns can serve as a "log" for monitoring when a row has changes
TS helpful to cut down on too much date parsing in PHP

No comments:

Post a Comment