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:
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?
Arguments for TIMESTAMP
DEFAULT CURRENT_TIMESTAMP
or ON UPDATE CURRENT_TIMESTAMP
(one column per table only until MySQL 5.6.5)FROM_UNIXTIME()
function - it will make it easier to write queries that can use indexesIn 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