42

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

CodeOverload
  • 47,274
  • 54
  • 131
  • 219

4 Answers4

24

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 :


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

Pascal MARTIN
  • 395,085
  • 80
  • 655
  • 663
  • 3
    They are limited _now_ to 32 bits :) – Tim Post Mar 28 '10 at 17:21
  • 4
    Who's using 32 bit systems in 2038? – Chris Baker Dec 21 '12 at 16:49
  • `new java.util.Date().getTime()` is already 64-bit. – Sergey Orshanskiy Oct 08 '13 at 01:25
  • 1
    Unix Timestamps, in a signed 32 bit integer, can store dates all the way back to 1901, as it can span from -2147483647 (December 13, 1901 8:45:53 PM UTC) to 2147483647 (January 19, 2038 3:14:07 AM UTC) – Luke Stevenson Aug 23 '17 at 06:54
  • 1
    @LukeStevenson, you've posted this in each question. I was wondering what were you trying to achieve by doing that other than for informing purposes? Is there any drawbacks to using UNIX timestamp you were trying to indicate? – LeanMan Sep 14 '20 at 06:53
  • @LeanMan I mentioned it as there is a persistent misconception that Unix Timestamps can only be used for date/times of 12:00AM 1 Jan 1970 UTC and later. But, date/times prior to that can be achieved by using negative integers. If a signed 32 bit integer is used, you can go back to 1901. – Luke Stevenson Sep 14 '20 at 06:56
  • Understood and thank you. Curious, do you know if there is any future solutions for dates past 2038? e.g., 64 bit UNIX timestamps? – LeanMan Sep 14 '20 at 22:34
15

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.

Michael Williamson
  • 11,308
  • 4
  • 37
  • 33
6

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.

Dor
  • 7,344
  • 4
  • 32
  • 45
  • 1
    Unix Timestamps, in a signed 32 bit integer, can store dates all the way back to 1901, as it can span from -2147483647 (December 13, 1901 8:45:53 PM UTC) to 2147483647 (January 19, 2038 3:14:07 AM UTC). So unless you are storing a date before 1901, a timestamp would work. – Luke Stevenson Aug 23 '17 at 06:55
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.

Tim Post
  • 33,371
  • 15
  • 110
  • 174
  • 2
    Unix Timestamps, in a signed 32 bit integer, can store dates all the way back to 1901, as it can span from -2147483647 (December 13, 1901 8:45:53 PM UTC) to 2147483647 (January 19, 2038 3:14:07 AM UTC) – Luke Stevenson Aug 23 '17 at 06:54