MySQL – datetime and timestamp

MySQL datatypes ‘datetime’ and ‘timestamp’ are often indistinguishable from their titles unless you read documentation thoroughly. Few years back, I was caught up in similar situation – I had a POS (Point of Sale) running on Sydney Timezone and a shared hosting server running on UTC timezone. I designed and deployed sales transactions schema which recorded sale information with its timestamp.

Issue? it started mysteriously converting timestamps from Australian timezone to UTC. Months worth of transactions churning invalid reports.

Approaching solution:¬†Re-synced db, checked all buffer scripts which carried the data. No clues found! Somehow, I went back to good ol’ documentation. Turns out – it was happening,¬†because datatype was set to ‘timestamp’ rather than ‘datetime’.

Solution: If you are working on MySQL 5.0+ and constant timestamps (i.e Рstoring values directly from one db to another) best to use datetime.

The main difference is that DATETIME is constant while TIMESTAMP is affected by your server’s time_zone setting.

Timezone should only be used if you intend to synchronize clusters across time zones.