2038年1月19日之后如何使用MySQL的from_unixtime函数？

Question

我们的日期存储为Unix时间戳。为了让用户根据他的时区设置搜索某个日期，我们曾经在查询中转换该时间戳，以确保搜索“2012-05-03”不会找到前一天/后一天的结果，这取决于用户设置的时区。

例如，如果一个日期被存储为2012-05-03 23:00 (UTC)，一个具有正确时区偏移量的用户搜索2012-05-04应该能够找到此条目。

目前的实现如下：

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

当然，这里的偏移量取决于用户的时区。

我们目前面临的问题是：Java可以成功地将2038年之后的日期存储为Unix时间戳。然而，MySQL方法from_unixtime由于其整数类型限制，不支持大于2147483647的值转换：

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

SELECT FROM_UNIXTIME(2147483648); //null

MySQL服务器本身是64位的，但 FROM_UNIXTIME 函数需要接受一个长整型参数。

目前我还没有找到合适的替代方法，有任何提示吗？

---

我们当然可以将时间戳作为长整型加载并在应用程序中处理它 - 但是为了 lazyloading ，我们还需要在查询过程中正确地进行转换。

- dognose

---

有什么理由不将数据类型更改为“datetime”吗？ - juergen d

@juergend 是的，不幸的是，数据是由应用程序的一部分创建的，我们无法更改它。（第三方库） - dognose

你尝试将时间戳列的数据类型更改为 bigint 了吗？ - juergen d

这个问题有一个10年前的错误/功能请求。 - Vatev

是的，但你可能需要等到2037年才能看到它 :) - Vatev

Answer 1

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

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

---

目前，我正在使用

---

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

如果有任何影响，应该尽量减少对性能的影响。

- dognose

---

似乎解决了问题，直到我遇到这个问题（GMT + 1时区）：SELECT FROM_UNIXTIME( 1469230446 ) 输出：2016-07-23 01:34:06SELECT DATE_ADD( FROM_UNIXTIME( 0 ) , INTERVAL 1469230446 SECOND ) 输出：2016-07-23 00:34:06有什么想法吗？ - Márton Tamás

@TamásMárton，使用正确的偏移量使用CONVERT_TZ。Unix时间始终为UTC。DATE_ADD使用您的时区GMT+1，因此您需要修复它。 - dognose

谢谢，将客户端时区设置为“+00:00”确实解决了问题。 - Márton Tamás

@TamásMárton，这也是一个选项。 （我也更喜欢在UTC时区下运行“服务器”，无论它实际部署在哪里） - dognose

是的，我完全同意。不幸的是，这是一个具有配置限制的共享Web托管。 - Márton Tamás