DATETIME vs TIMESTAMP in MySQL, which one to use?

It is simple but confusing nonetheless. Official documentation does not help much, as both definitions looks almost the same.

But there is a difference, which is important if your code handles time zones.

With TIMESTAMP MySQL converts the value to UTC timezone to store it in database. Consequently, to SELECT the data MySQL runs a convertion from UTC to the current time zone.
For example, you need TIMESTAMP if date/time reflects individual users timezone.

DATETIME is stored/fetched unchanged.

Keep in mind that if time zone settings change between storage and retrieval, you will get back different values for TIMESTAMP.

Bottom line – use TIMESTAMP for reference to an absolute instant of time. Use DATETIME for consistency.

Leave a Reply

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