MySQL: What's the best to use, Unix TimeStamp Or DATETIME

Question

Probably many coders want to ask this question. it is What's the adventages of each one of those MySQL time formats. and which one you will prefer to use it in your apps.

For me i use Unix timestamp because maybe i find it easy to convert & order records with it, and also because i never tried the DATETIME thing. but anyways i'm ready to change my mind if anyone tells me i'm wrong.

Thanks

Answer 1

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