mysql utc timestamp,如何在MySQL中从UNIX_TIMESTAMP（）获取UTC日期时间

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

But, I should not use CONVERT_TZ.

(because Could not use timezone function in partitioning.)

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)

)

解决方案

I think your problem is not CONVERT_TZ, but FROM_UNIXTIME.

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

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 can only handle parameters upto 2147483647 - Which means, it works up until 2038-01-19 04:14:07

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

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

from_unixtime (x)

use

date_add(from_unixtime(0), INTERVAL x second)

Result(s):

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