Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

What are good reasons to choose TIMESTAMP over DATETIME columns in MySQL?

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 setting
  • TS columns can serve as a "log" for monitoring when a row has changes

The 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 .....

like image 623
Dan Soap Avatar asked Feb 07 '10 22:02

Dan Soap


1 Answers

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.

like image 130
Xorlev Avatar answered Oct 13 '22 23:10

Xorlev