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

Question

Hello,

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

Answer 2

Unless digitizing records prior to January 1, 1970, I like the UNIX epoch. Its just a matter of preference, whole unsigned numbers are simpler to deal with when using multiple languages.

Just keep in mind, the epoch starts at January 1, 1970. A lot of companies had been in business for decades, if not longer, prior to that.

Answer 3

As others have said, timestamps can represent a smaller range of datetimes (from 1970 to 2038). However, timestamps measure the number of seconds since the Unix Epoch (1970-01-01 00:00:00 UTC), thereby making them independent of time zone, whereas DATETIME stores a date and time without a time zone. In other words, timestamps unambiguously reference a particular point in time, whereas the exact point in time a DATETIME refers to requires a time zone (which is not stored in a DATETIME field). To see why this can matter, consider what happens if we change our time zone.

Let's say we want to store the datetime 2010-03-27 12:00 UTC. If we store this and retrieve it using a timestamp or DATETIME, then there usually appears to be no difference. However, if the server now changes so that the local time zone is UTC+01, then we get two different results if we pull out the datetime.

If we'd set the field to a DATETIME, it would report the datetime as 2010-03-27 12:00, despite the change in time zone. If we'd set the field to a timestamp, the date would be reported as 2010-03-27 11:00. This isn't a problem with either datatype -- it's just a result of the fact that they store slightly different information.

Answer 4

That really depends. I'll give you 2 examples where one overcome the other:

Timestamp is better than DATETIME when you want to store users session in the database and the session creation time (in Timestamp format) is used for fast row retrieval (with index).
E.g. table may look like this:
[session_create_time AS Timestamp][IP_address AS 32bit Int][etc...]
Having an index on the first two columns can really speed up your queries. If you had a DATETIME value type for the session_create_time field, then it could be taken much more time. Take into account that session queries are executed each time a user request a page, so efficiency is crucial.

DATETIME is better than Timestamp when you want to store a user's date of birth or some historic events that require flexible time range.