TIMESTAMP (as INT) vs DATETIME!

[Hedge]

I’m curious I couldn’t find a post about it.

Just want to know which one to use. I personally don’t like DATETIME too much because some conversions on PHP side have to be done to output a date with locales-settings.

I also need to know if TIMESTAMP (as INT to prevent changes) vs. DATE is performing the same as vs. DATETIME but I think it is pretty much the same.

So I want to decide to use TIMESTAMP (as INT) fulltime or DATE and DATETIME

[MarkRose]

I believe the performance is the same, but DATE/DATETIME lets you do additional calculations easily in MySQL.

[Hedge]

Thanks for the late reply.

Can anyone confirm this?

[r00ps]

Hi,

I personally prefer using an unsigned int field containing UTC as you can index it for good performance and you don’t get any funny business when daylight saving changes happen.

We use it with a PHP application that alters the displayed time depending on a user’s timezone and it works very well.

rups

[brcavnagh]

I personally try to avoid using timestamps in mysql because they tend to be too costly. Its not always true, but generally true. Its not costly in the sense of storage space or correctness, its costly in the sense that if you want to use any mysql functions or do some sort of date operation, you are going to have to convert to datetime anyway and thats a cost. You might need precise conversions, which means you need to compensate for leap years or DST conversion either in US or even worse internationally. Its a pretty straight up process if you are using datetime fields, its a nightmare if you are using timestamps. Plus generally if you are doing ops on timestamps directly it makes your sql less readable if you are say joining between ints as opposed to dates.

In the end, database design is all about managing cost, both the cost of inputing data as well as getting it out.