如何在MySQL中从UNIX_TIMESTAMP()获取UTC日期时间 [英] How to Get UTC Datetime from UNIX_TIMESTAMP() in MySQL

问题描述

我想知道如何从mysql中的unix_timestamp获取utc日期时间.

I want to know that how to get utc datetime from unix_timestamp in mysql.

但是，我不应该使用CONVERT_TZ.

But, I should not use CONVERT_TZ.

(因为无法在分区中使用时区功能.)

(because Could not use timezone function in partitioning.)

错误发生在SQL模式中...

The error occurs in the SQL schema...

CREATE TABLE `table` (
  `idx` BIGINT(20) NOT NULL,
  etc...
) ENGINE=InnoDB DEFAULT CHARSET=utf8
PARTITION BY RANGE( YEAR(CONVERT_TZ(from_unixtime(`idx` >> 24), @@session.time_zone, '+00:00')) )
SUBPARTITION BY HASH ( MONTH(CONVERT_TZ(from_unixtime(`idx` >> 24), @@session.time_zone, '+00:00')) )
SUBPARTITIONS 12 (
    PARTITION p2016 VALUES LESS THAN (2016),
    PARTITION p2017 VALUES LESS THAN (2017),
    PARTITION p2018 VALUES LESS THAN (2018),
    PARTITION p2019 VALUES LESS THAN (2019),
    PARTITION p2020 VALUES LESS THAN (2020)
)

推荐答案

我认为您的问题不是CONVERT_TZ，而是FROM_UNIXTIME.

I think your problem is not CONVERT_TZ, but FROM_UNIXTIME.

FROM_UNIXTIME以整数作为参数-表示32位.

FROM_UNIXTIME takes an Integer as argument - which means 32 bit.

如果您使用今天的unix时间戳:1480546792，则右移24位-您刚刚超出了unix_time上有效参数的32位限制.

If you take todays unix-timestamp: 1480546792, shifted right 24 bit - you are just exceeding the 32-bit limit for a valid parameter on unix_time.

from_unixtime最多只能处理2147483647的参数-这意味着它可以工作到2038-01-19 04:14:07

from_unixtime can only handle parameters upto 2147483647 - Which means, it works up until 2038-01-19 04:14:07

我也遇到了这个问题，自2002年以来，此问题的修复程序正在开发中".

I've encountered this problem as well, and since 2002 a fix for this is "under development".

在最终解决之前，应使用date_add解决方法.代替

Until it has finally be resolved, you should use a workaround, using date_add. Instead of

from_unixtime (x)

使用

date_add(from_unixtime(0), INTERVAL x second)

结果:

SELECT from_unixtime (2147483647); //2038-01-19 04:14:07 
SELECT from_unixtime (2147483648); //NULL

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

这篇关于如何在MySQL中从UNIX_TIMESTAMP()获取UTC日期时间的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！