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.
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.