47

I'm in a dilemma about saving date and time values in MySQL's TIMESTAMP format vs in a custom UNSIGNED INT format. The main considerations here are speed of retrieval, appropriate range calculations in PHP and occasional formatting into human readable values.

The storage space required for each type and their ranges:

DATETIME        8 bytes  '1000-01-01 00:00:00' to '9999-12-31 23:59:59'
TIMESTAMP       4 bytes  '1970-01-01 00:00:01' UTC to '2038-01-19 03:14:07' UTC
UNSIGNED INT    4 bytes  (Maximum Value 4294967295)

I dont need the range of DATETIME at all. I'm torn between TIMESTAMP and UNSIGNED INT.

Arguments in favor of UNSIGNED INT:

  • A UNIX timestamp of 4294967295 converts to Sun, 07 Feb 2106 06:28:15 GMT which is more than TIMESTAMP and good enough for me
  • Comparing these timestamps directly in PHP would be faster rather than converting TIMESTAMPs via strtotime() and then comparing them

The only advantage TIMESTAMP would give me is when I'm reading in the values from the mysql table manually and need to 'see' them.

Is there any compelling reason to use TIMESTAMP and not an UNSIGNED INT?

siliconpi
  • 8,105
  • 18
  • 69
  • 107
  • See related: [datetime vs timestamp?](http://stackoverflow.com/questions/409286/datetime-vs-timestamp) – JYelton Aug 11 '11 at 16:13
  • A 32bit timestamp will only go until Jan 2038, and many libraries are still using 32bit time_t rather than 64bit, so you may run into portability issues. Internally, PHP stores the timestamps/datetime in numeric format anyways, and only converts to the nice yyyy-mm-dd type strings on retrieval. – Marc B Aug 11 '11 at 16:21

4 Answers4

32

Arguments for TIMESTAMP

  • It implicitly stores data in UTC time zone. No matter what your session time-zone is. Useful if you need to use different time zones.
  • You can have automated timestamping columns using DEFAULT CURRENT_TIMESTAMP or ON UPDATE CURRENT_TIMESTAMP (one column per table only until MySQL 5.6.5)
  • You can use datetime function for date comparison, addition, subtraction, range lookup etc, without the need to use FROM_UNIXTIME() function - it will make it easier to write queries that can use indexes

  • In PHP 

    >> date('Y-m-d h:i:s',4294967295);
'1969-12-31 11:59:59'

    so the range is in fact the same

When UNIX_TIMESTAMP() is used on a TIMESTAMP column, the function returns the internal timestamp value directly, with no implicit “string-to-Unix-timestamp” conversion

Achraf Almouloudi
  • 756
  • 10
  • 27
Mchl
  • 61,444
  • 9
  • 118
  • 120
  • 1
    `echo date('Y-m-d h:i:s',4294967295);` for me gives 2106-02-07 06:28:15 - are you using a 32-bit OS? – Tim Fountain Aug 11 '11 at 16:43
  • 1
    No, it's a 64b Windows, but I think I have a 32b PHP running. It is soomething you need to take into account if you want your application to be portable. If you will have the control on production environement, then nevermind. – Mchl Aug 11 '11 at 16:44
  • 2
    As for your second bullet, only one column can be made DEFAULT CURRENT_TIMESTAMP or ON UPDATE CURRENT_TIMESTAMP, as pointed out here (at least on MySQL v5.1): http://dev.mysql.com/doc/refman/5.1/en/timestamp-initialization.html – Boaz Rymland Apr 23 '12 at 18:34
  • 1
    @Boaz Rymland: That is right. This changes with MySQL 5.6.5, which also adds autoinit values for DATETIME datatype. http://dev.mysql.com/doc/refman/5.6/en/timestamp-initialization.html – Mchl Apr 27 '12 at 21:58
  • 1
    Shouldn't timestamps be stored as UTC? – JoeTidee Aug 18 '15 at 10:53
  • They are. In the answer above I wrote it's GMT, but it is in fact UTC. – Mchl Aug 18 '15 at 17:14
8

The only real use for TIMESTAMP is when you want that field to be updated automatically when the row is updated (which is the default behaviour for that field), or when data storage requirements are so strict that 4 bytes per row really makes a difference to you.

Really the comparison should be between DATETIME and UNSIGNED INT, and I'd recommend DATETIME because:

  • You can use MySQL's native date/time functions for selecting by date ranges etc.
  • It is trivially easy to select these dates out as UNIX timestamps for easy formatting in PHP: SELECT UNIX_TIMESTAMP(field) FROM table, no need to select out the raw value and use strtotime
  • Easier to read and edit the fields in your database directly if you need to (as you pointed out).
  • No limitations on date range

Point two alone really removes any reason to store in integers, in my opinion.

Tim Fountain
  • 33,093
  • 5
  • 41
  • 69
  • 4
    The downside for DATETIME is that it doesn't store the GMT offset in the field. You either need to convert everything before storing to GMT (or whatever you want your 'zulu' time to be) and then convert it back if you're dealing with multiple timezones. Storing in INT removes some of these requirements. – ashurexm Aug 11 '11 at 16:46
  • 1
    An upside for DATETIME is that I'm lazy and it works pretty good for a single locale/timezone. – ashurexm Aug 11 '11 at 16:47
  • 1
    Thanks for the comment on `UNIX_TIMESTAMP` - I didn't know of this before now. – Niels Abildgaard May 01 '13 at 11:58
  • I have been storing everything as an unsigned int with Carbon DateTime library... this is interesting information to consider. +1! – Jethro Hazelhurst Nov 09 '16 at 12:58
  • 2
    How do you deal with daylight saving time if you use DATETIME? When we rewind the clock one hour, we get to the same time as one hour earlier. You don't have that potential problem with Unix time. – Peppe L-G May 11 '18 at 08:12
  • I consider it best practice to store everything in UTC. If you store local timezones one should also be able to determine the timezone to handle time differences and daylight savings. I think that if you'd store DATETIME values from multiple local timezones and somehow not be able to get the timezone you would have much bigger problems than dealing with daylight savings. –  Mar 15 '19 at 02:44
6

This might not be a "scientific" answer but I always find the way MySql handles conversion, arithmetics, comparsion, etc... on TIMESTAMP columns confusing. An UNSIGNED INT column is much more straight forward and I always know what to expect.

P.S. Perhaps one other thing in favor of TIMESTAMP column is its ability to be automatically set to current time after each update or insert but that is not something you can't live without.

nobody
  • 10,599
  • 4
  • 26
  • 43
1

As always it depends on what you need to save.
For example if you are consuming data from some API and it sends to you the time as a number (seconds, this is common in market data) then could be easier and faster just to store it as unsigned int instead of converting everytime to a string before inserting it.

Enrique
  • 4,693
  • 5
  • 51
  • 71