MySQL from_unixtime在2038-01-19之后? [英] MySQL from_unixtime after 2038-01-19?

问题描述

我们将日期存储为unix时间戳.为了允许用户搜索某个日期-根据他的时区设置，我们曾经在查询中转换该时间戳，以确保搜索"2012-05-03"不会找到上一个/下一个的结果一天，具体取决于用户设置的时区.

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.

即如果日期存储为2012-05-03 23:00 (UTC)，则具有正确时区偏移量的用户在搜索2012-05-04时应找到此条目.

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.

我们目前面临的问题:Java成功地将2038年之后的日期存储为unix时间戳.但是，由于整数类型限制，MySQL方法from_unixtime不支持大于2147483647的值的任何转换:

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

MySQL服务器本身是64位的，但是ofc. FROM_UNIXTIME需要接受long参数.

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?

我们可以.将时间戳加载为Long并在应用程序中处理-但是对于 lazylaoding ，我们还需要能够在查询期间正确转换它.

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.

推荐答案

一种解决方法可能是使用DATE_ADD，但是我不确定它在性能方面的表现如何:

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.

这篇关于MySQL from_unixtime在2038-01-19之后?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！