Search code examples
sqldatetimetimestampfutureunix-timestamp

SQL: How to convert future date to timestamp with UNIX_TIMESTAMP()?

I want to convert a DATETIME from a column to a UNIX TIMESTAMP. But the thing is that those dates are in a distant future, such as 2066-09-01... You can try those simple queries:

SELECT UNIX_TIMESTAMP( '2016-09-03 09:00:00' ) returns 1472886000 -> GOOD

SELECT UNIX_TIMESTAMP( '2036-09-03 09:00:00' ) returns 2104038000 -> GOOD

SELECT UNIX_TIMESTAMP( '2066-09-03 09:00:00' ) returns 0 -> BAD! WHY??

Any idea? Any workaround ?

Solution

  • Sounds like your rdbms is suffering from the Y2038 problem: https://en.m.wikipedia.org/wiki/Year_2038_problem

    Is this MySQL? They have a bug about this: https://dev.mysql.com/worklog/task/?id=1872

    Suggest handling this outside SQL if possible. PHP's strtotime() and similar functions in other languages can play nice with MySQL's ISO date format.