Should I use the datetime or timestamp data type in MySQL?

koni picture koni · Jan 3, 2009 · Viewed 913.2k times · Source

Would you recommend using a datetime or a timestamp field, and why (using MySQL)?

I'm working with PHP on the server side.

Answer

blivet picture blivet · Jan 3, 2009

Timestamps in MySQL are generally used to track changes to records, and are often updated every time the record is changed. If you want to store a specific value you should use a datetime field.

If you meant that you want to decide between using a UNIX timestamp or a native MySQL datetime field, go with the native format. You can do calculations within MySQL that way ("SELECT DATE_ADD(my_datetime, INTERVAL 1 DAY)") and it is simple to change the format of the value to a UNIX timestamp ("SELECT UNIX_TIMESTAMP(my_datetime)") when you query the record if you want to operate on it with PHP.