Categories
datetime mysql sqldatatypes timestamp

Should I use the datetime or timestamp data type in MySQL?

3017

Would you recommend using a datetime or a timestamp field, and why (using MySQL)?

I’m working with PHP on the server side.

3

2042

Timestamps in MySQL are generally used to track changes to records, and are often updated every time the record is changed. If you want to store a specific value you should use a datetime field.

If you meant that you want to decide between using a UNIX timestamp or a native MySQL datetime field, go with the native DATETIME format. You can do calculations within MySQL that way
("SELECT DATE_ADD(my_datetime, INTERVAL 1 DAY)") and it is simple to change the format of the value to a UNIX timestamp ("SELECT UNIX_TIMESTAMP(my_datetime)") when you query the record if you want to operate on it with PHP.

4

  • 1124

    An important difference is that DATETIME represents a date (as found in a calendar) and a time (as can be observed on a wall clock), while TIMESTAMP represents a well defined point in time. This could be very important if your application handles time zones. How long ago was ‘2010-09-01 16:31:00’? It depends on what timezone you’re in. For me it was just a few seconds ago, for you it may represent a time in the future. If I say 1283351460 seconds since ‘1970-01-01 00:00:00 UTC’, you know exactly what point in time I talk about. (See Nir’s excellent answer below). [Downside: valid range].

    Sep 1, 2010 at 14:36

  • 133

    Another one difference: queries with “native” datetime will not be cached, but queries with timestamp – will be.

    – OZ_

    Apr 28, 2011 at 17:37

  • 53

    “Timestamps in MySQL generally used to track changes to records” Do not think that’s a good answer. Timestamp are a lot more powerful and complicated than that as MattBianco and Nir sayd. Although, the second part of the answer is very good. It’s true what blivet said, and is a good advise.

    May 16, 2011 at 14:00


  • 3

    I was the one to upvote to 2000 😛

    – albanx

    Mar 12 at 14:09

1020

In MySQL 5 and above, TIMESTAMP values are converted from the current time zone to UTC for storage, and converted back from UTC to the current time zone for retrieval. (This occurs only for the TIMESTAMP data type, and not for other types such as DATETIME.)

By default, the current time zone for each connection is the server’s time. The time zone can be set on a per-connection basis, as described in MySQL Server Time Zone Support.

3

  • 15

    this OReilly presentation is very good for this topic (PDF sorry) cdn.oreillystatic.com/en/assets/1/event/36/…

    – gcb

    Oct 27, 2013 at 2:11


  • 19

    Its also about the nature of the event: – A video-conference (TIMESTAMP). All attendants should see a reference to an absolute instant of time adjusted to its timezone. – A local task time (DATETIME), i should do this task at 2014/03/31 9:00AM no matters if that day i’m working in New York or Paris. I will start to work at 8:00AM of local time of place i’ll be that day.

    – yucer

    Dec 17, 2013 at 10:40


  • Yes, and this is just horrible. The DBMS should never, ever convert timestamps in any direction nor taking the current DB’s system time into account. It should just save the internal timestamp as it is (ms since epoch). When rendering the value (in the very last moment) the value should be presented (!) in the user’s timezone from the application. Everything else is just pure pain. If any, the DBMS should support explicit types for local and absolute time where ‘local’ is something like your birthday or ‘noon’ and ‘absolute’ is something like the start time of a rocket.

    – spyro

    Nov 11, 2021 at 20:13


593

I always use DATETIME fields for anything other than row metadata (date created or modified).

As mentioned in the MySQL documentation:

The DATETIME type is used when you need values that contain both date and time information. MySQL retrieves and displays DATETIME values in ‘YYYY-MM-DD HH:MM:SS’ format. The supported range is ‘1000-01-01 00:00:00’ to ‘9999-12-31 23:59:59’.

The TIMESTAMP data type has a range of ‘1970-01-01 00:00:01’ UTC to ‘2038-01-09 03:14:07’ UTC. It has varying properties, depending on the MySQL version and the SQL mode the server is running in.

You’re quite likely to hit the lower limit on TIMESTAMPs in general use — e.g. storing birthdate.

4

  • 224

    you can also hit the upper limit easily if you are in banking or real estate… 30-year mortgages go beyond 2038 now

    – Kip

    Aug 10, 2012 at 14:47


  • 22

    Of course, use 64-bit Unix timestamps. For example, in Java, new Date().getTime() already gives you a 64-bit value.

    Oct 8, 2013 at 1:16


  • 13

    No idea. It’s a much larger problem than just MySQL and there’s no simple fix: en.wikipedia.org/wiki/Year_2038_problem I don’t believe MySQL can just declare timestamps are now 64-bit and assume everything will be fine. They don’t control the hardware.

    – scronide

    Feb 10, 2015 at 18:11

  • So timestamp is stored internally (presumably) as a Unix timestamp – an integer. It’s quick to insert and retrieve, but propably slower when applying date fucntions to it, as it will need converting to an internal datetime format to do this. So use timestamp for simple logging, where it will be fast, but less flexible and with a smaller range.

    – Jason

    Apr 19 at 12:11