Disclaimer: This might be a duplicate of datetime vs. timestamp?, but I feel I am not convinced of the answers:
My question is:
What are the good reasons to choose TIMESTAMP over DATETIME columns in MySQL?
I'm pretty sure I don't know everything there is about the differences between those two, so I will try to list the main advantages of DATETIME (DT
) compared to TIMESTAMP (TS
) to my knowledge:
DT
is human readable (TS
is not without using TO_DATE
)DT
has a possble 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
fields seem to perform better (according to this blog post)DT
can be used for advanced date calculation (SELECT NOW() + INTERVAL 2 DAY
)And vice versa:
TS
only needs 4 bytes (DT
uses 8)TS
are stored as UTC values and changed according to the client's timezone settingTS
columns can serve as a "log" for monitoring when a row has changesThe only reason I see for using it then would be the row monitoring, but looking at the range of TIMESTAMP
, which will end in "only" 28 years*, I would rather go with an insert or update trigger.
So, what am I missing? I still don't see a really good reason for choosing timestamp for any purpose?
* I know, this is appears quite long, but back in the 60s some engineers also decided to shave off 2 bytes of the year field, because their computer systems would never run until the year 2000 .....
DT columns can also serve as a "log" for monitoring when a row changes, I use them and triggers to do that.
I usually use timestamp when working with a lot of PHP stuff as to cut down on too much date parsing. If the need ever arises, I can update my software to use datetime.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With