Why does MySQL unix time stop short of the 32 bit unsigned integer limit?

Question

mysql> SELECT FROM_UNIXTIME(2145916799), FROM_UNIXTIME(2145916800), POW(2,32-1)-1, 2145916799 - POW(2,32-1)-1;
+---------------------------+---------------------------+---------------+----------------------------+
| FROM_UNIXTIME(2145916799) | FROM_UNIXTIME(2145916800) | POW(2,32-1)-1 | 2145916799 - POW(2,32-1)-1 |
+---------------------------+---------------------------+---------------+----------------------------+
| 2037-12-31 18:59:59       | NULL                      |    2147483647 |                   -1566850 | 
+---------------------------+---------------------------+---------------+----------------------------+
1 row in set (0.00 sec)

mysql> 

The first field is the highest possible value I can give to FROM_UNIXTIME. The next field is that value plus one which returns NULL. The third field is the highest possible value for an unsigned 32 bit int. The final value is the difference between the highest possible UNIXTIME and the highest possible int which is a little over 18 days worth of seconds. It appears that it stops at the end of 2037 in the local timezone. Any ideas why? Is that a natural breaking point in one of the calculations? Is that just an arbitrary limit in mysqld?

Quaff

good question. Might be something to do with timezones. – Ironmaster

A workaround for getting formatted dates after 2038-01-19 03:14:07 from unix timestamps is explained at https://mcmap.net/q/1625567/-how-to-get-utc-datetime-from-unix_timestamp-in-mysql – Autum

Answer 1

normally unix timestamp range is from January 1st 1970 to December 31st 2037 for more information have a look at http://en.wikipedia.org/wiki/Year_2038_problem

Atencio

Answer 2

I got very different results in GMT+0200. same results for both i686 and x86_64.

Propably 2038-01-01 UTC was not allowed.

SELECT FROM_UNIXTIME(2145916799), FROM_UNIXTIME(2145916800), POW(2,32-1)-1, 2145916799 - POW(2,32-1)-1;
+---------------------------+---------------------------+---------------+----------------------------+
| FROM_UNIXTIME(2145916799) | FROM_UNIXTIME(2145916800) | POW(2,32-1)-1 | 2145916799 - POW(2,32-1)-1 |
+---------------------------+---------------------------+---------------+----------------------------+ 
| 2038-01-01 01:59:59       | 2038-01-01 02:00:00       |    2147483647 |                    -1566850 |  
+---------------------------+---------------------------+---------------+------------------- ---------+
1 row in set (0.00 sec)

mysql> \s
--------------
mysql  Ver 14.12 Distrib 5.0.77, for redhat-linux-gnu (i686) using readline 5.1

Impasto

I am in US Eastern time so that would explain the difference. – Quaff