Alternative for MySQL UNIX_TIMESTAMP()

In 2038, 32-bit systems and 32-bit code has a problem. It’s well documented online, so if you do not know much about it, check it out on Wikipedia then come back and continue reading here. Even if you are running MySQL on a 64-bit machine, MySQL is not—at least not fully.

Ideally you would want to use the scripting language running your application to do the conversion. For example, place your date string from your result and run it through strtotime() or the DateTime class in PHP. But let’s assume that you are unable to do that for whatever crazy reason and you need to have the date and time as an Unix timestamp in your query results. What do you do?

Assuming we still use MySQL in 2038 and the problem isn’t fixed by then or you are working with future dates that may go into or past 2038 before we get there, there is a way around it. If you use:

TO_SECONDS(`datetime`)-62167219200+TO_SECONDS(UTC_TIMESTAMP())-TO_SECONDS(NOW())

instead of:

UNIX_TIMESTAMP(`datetime`)

in your MySQL queries, you will get exactly the same Unix timestamp, but future ready. It will continue to work in and past 2038.

Just execute this query to see it demonstrated:

SELECT
    `datetime`,
    UNIX_TIMESTAMP(`datetime`) AS `timestamp`,
    TO_SECONDS(`datetime`)-62167219200+TO_SECONDS(UTC_TIMESTAMP())-TO_SECONDS(NOW()) AS `timestamp_alt`
    # NOTE: 62167219200 == TO_SECONDS('1970-01-01 00:00:00')
FROM
    (
        (
            SELECT NOW() AS `datetime`
        )
        UNION ALL
        (
            SELECT DATE_ADD(NOW(), INTERVAL 30 YEAR) AS `datetime`
        )
    ) `datetest`

This works in MySQL 5.5 or later. If you are stuck using a version older than 5.5 or need to be backwards compatible, it’s a little more complicated:

((TO_DAYS(`datetime`)-719528+TO_DAYS(UTC_DATE())-TO_DAYS(CURDATE()))*86400)+TIME_TO_SEC(TIME(`datetime`))+TIME_TO_SEC(UTC_TIME()-CURTIME())
# NOTE: 719528 == TO_DAYS('1970-01-01 00:00:00')
# NOTE: 86400 == seconds in a day

Now, go forth and be ready for the 2038 problem… at least in your MySQL queries. If you find any place for improvement, please feel free to share your thoughts in the comments. Thanks for reading!

Leave a Reply

Your email address will not be published. Required fields are marked *