MySQL from_unixtime after 2038-01-19?

Question

We have dates stored as a unix timestamp. To allow a user to search for a certain date - based on his timezone-setting, we used to convert that timestamp inside the query, to make sure a search for "2012-05-03" will not find results of the prior / next day depending on which timezone the user has setup.

i.e. if a date is stored as 2012-05-03 23:00 (UTC) A user with the proper timezone offset searching for 2012-05-04 should find this entry.

This is done like this at the moment:

CONVERT_TZ(FROM_UNIXTIME(`javaTimeStampColumn`/1000),'+00:00','+00:00')

where ofc. the offsets are set depending on the users timezone.

The problem we are facing at the moment: Java successfully stores dates after the year 2038 as a unix-timestamp. The MySQL method from_unixtime however does not support any conversion of values greater than 2147483647 due to it's integer type limitation:

SELECT FROM_UNIXTIME(2147483647); //2038-01-19 04:14:07

SELECT FROM_UNIXTIME(2147483648); //null

The MySQL server itself is 64bit, but ofc. FROM_UNIXTIME would need to accept a long as argument.

I could not find a proper replacement by now, any hints?

---

We could ofc. load the timestamp as a Long and handle it in the application - But for lazylaoding we need to be able to convert it correctly during the query as well.

Amery

Any reason not to change the data type to datetime? – Peppermint

@juergend Yes, unfortunately the data is created by a part of the application where we can not change this. (3rd party library) – Amery

Did you try changing the data type of your timestamp column to bigint? – Peppermint

Are you using timestamp fields or int's which have timestamps in them? – Hanaper

there is no built-in replacement. you can trivially store the 64bit timestamp as a bigint, but there won't be any built-in mysql functions to convert that to a date/time format. – Miki

it's BIGINT(20) columns. – Amery

There is a 10 year old bug/feature request for this. – Hanaper

@Hanaper Support for 64-bit TIMESTAMP data (or similar) will be part of a later release. they said :-) – Amery

Yes, but you might need to wait until 2037 for it :) – Hanaper

@Hanaper It looks like they will never fix it! ("The behavior of the TIMESTAMP type is also unaffected by this change; its maximum allowed value remains '2038-01-19 03:14:07.999999' UTC, regardless of platform. For dates futureward of this, use the MySQL DATETIME type instead.") – Dictum

Answer 1

A workaround might be to use DATE_ADD, but I'm not sure how it behaves performance-wise:

SELECT DATE_ADD(FROM_UNIXTIME(0), INTERVAL 2147483647 SECOND); //2038-01-19 04:14:07
SELECT DATE_ADD(FROM_UNIXTIME(0), INTERVAL 2147483648 SECOND); //2038-01-19 04:14:08
...
SELECT DATE_ADD(FROM_UNIXTIME(0), INTERVAL 4147483647 SECOND); //2101-06-06 07:47:27

---

So for now, I'm using

...
CASE 
  WHEN `javaTimeStampColumn` > 2147483647 THEN
    CONVERT_TZ(DATE_ADD(FROM_UNIXTIME(0), INTERVAL `javaTimeStampColumn`/1000 SECOND),'+00:00','+00:00')
  ELSE  
    CONVERT_TZ(FROM_UNIXTIME(`javaTimeStampColumn`/1000), '+00:00','+00:00')
END as ts
FROM table
...

which should minimize the impact on performance if there is any.

Amery

It seemed to solve the problem, until I stumbled across this (GMT+1 timezone): SELECT FROM_UNIXTIME( 1469230446 ) makes: 2016-07-23 01:34:06 SELECT DATE_ADD( FROM_UNIXTIME( 0 ) , INTERVAL 1469230446 SECOND ) makes: 2016-07-23 00:34:06 Any idea? – Braque

@TamásMárton, use CONVERT_TZ with proper offsets. Unix-Time is always UTC. DATE_ADD is using your Timezone of GMT+1, so you need to fix this. – Amery

Thanks, setting the client timezone to '+00:00' fixed it really. – Braque

@TamásMárton that's an option as well. (I also prefer to have the "Server" running in UTC-Timezone no matter where it is actually deployed) – Amery

Yeah, I totally agree. Unfortunately, this is a shared web hosting with configuration limitations (: – Braque

Answer 2

Looks like the Epochalypse for MySQL has been increased to Jan 18 3001, 11:59:59pm UTC (screenshot in PST)

Dictum

You're right, since 8.0.28: On 64-bit platforms running MySQL 8.0.28 or later, the effective maximum is 32536771199.999999, which returns '3001-01-18 23:59:59.999999' UTC. Regardless of platform or version, a greater value for unix_timestamp than the effective maximum returns 0. dev.mysql.com/doc/refman/8.0/en/… – Amery

@Amery Right, 3:59:59 was in PST – Dictum