I was asked a question today, “DATETIME vs TIMESTAMP. When to use which & why?”
It’s a good MySQL introduction question, here are some general considerations for choosing one.
Do you need Date values other then an EPOCH value? (i.e. before 1970) If the answer is yes, then DATETIME is required.
If you do not however, then TIMESTAMP is the best choice for a few reasons.
- The TIMESTAMP columns uses 4 Bytes to record it’s value, while DATETIME uses 8 bytes. Using the smallest storage is always a best practice for all columns.
- The TIMESTAMP column supports the CURRENT_DATE syntax in the CREATE TABLE command. This enables the column to have a default value for INSERT or for UPDATE, but not both. Indeed this is the only data type that allows for any default value that is not a constant.
- All date functions (at least the ones I use) work equally as well with TIMESTAMP and DATETIME.
I have yet to find any benchmarking to indicate any performance differences of not selecting TIMESTAMP.
And just for a piece of trivia, the DATE datatype is 3 bytes, the TIME datetype is 3 bytes, so why is the DATETIME 8 bytes?
Yes, for those that intend to reply I do know the answer, however others readers may not. Comments please!
