Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Using MySQL's TIMESTAMP vs storing timestamps directly

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?

like image 328
siliconpi Avatar asked Aug 11 '11 16:08

siliconpi


People also ask

Is it better to use TIMESTAMP or datetime?

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.

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

The DATETIME type is used for values that contain both date and time parts. 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 is used for values that contain both date and time parts.

Does TIMESTAMP require more storage than datetime?

Size − Datetime requires 5 bytes along with 3 additional bytes for fractional seconds' data storing. On the other hand, timestamp datatype requires 4 bytes along with 3 additional bytes for fractional seconds' data storing.

What is the difference between datetime and TIMESTAMP data type?

Just as DATETIME , the TIMESTAMP data type contains both the date and the time in the following format YYYY-MM-DD hh:mm:ss . However, unlike DATETIME , the TIMESTAMP data type has a fixed range between 1970-01-01 00:00:01 UTC to 2038-01-19 03:14:07 UTC.


1 Answers

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

    • You can still retrieve integer unix timestamp with no additional overhead using UNIX_TIMESTAMP() function: http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_unix-timestamp

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

like image 185
Mchl Avatar answered Sep 23 '22 16:09

Mchl