Should we store datetime or unix timestamp in MySQL?

Timestamps in MySQL generally used to track changes to records, and are 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.

Timestamp (both PHP ones and MySQL’s ones) are stored using 32 bits (i.e. 4 bytes) integers ; which means they are limited to a date range that goes from 1970 to 2038.

DATETIME don’t have that limitation — but are stored using more bytes (8 bytes, if I’m not mistaken)

After, between storing timestamps as seen by PHP, or timestamps as seen by MySQL :

using PHP timestamps means manipulations are easier from PHP — see Date/Time Functions

using MySQL’s timestamps means manipulations are easier from MySQL — see 11.6. Date and Time Functions

And, for more informations between MySQL’s TIMESTAMP and DATETIME datatypes, see 10.3.1. The DATETIME, DATE, and TIMESTAMP Types


Leave a Reply

Your email address will not be published. Required fields are marked *