How to Get UTC Datetime from UNIX_TIMESTAMP() in MySQL

Question

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

Please see: https://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_utc-timestamp — mba12, Nov 30 '16 at 22:56
`SUBPARTITION` and `BY HASH` are almost always useless; what was the purpose in using them? — Rick James, Dec 01 '16 at 06:44

dognose · Answer 1 · 2016-11-30T23:22:06.860

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

Thanks for your answer. but my `idx` colume format is (UNIX_TIMESTAMP() << 24 + other number[24bit]). Just i want to partitioning by year and month. — user212942, Nov 30 '16 at 23:17
@user212942 can you provide an example value for `idx`, where it fails? — dognose, Nov 30 '16 at 23:19

O. Jones · Answer 2 · 2016-11-30T23:37:28.673

You are in a bind here. Whenever you render a TIMESTAMP data item as a date / time item, MySQL implicitly converts it from UTC to local time(where local time is controlled by current connection's time_zone setting.

That makes it infeasible to use YEAR(timestamp) to control partitioning. Pretty much anything you do with MySQL date and time functions to extract the year will perform the conversion. That means any such conversion will be nondeterministic. That means you can't use it to control partitioning.

You could use UNIX_TIMESTAMP() DIV ( 365 * 24 * 60 *60) or some such approximation of years to control your partitioning. It would be close, inexact, and deterministic.

Or you could use UNIX_TIMESTAMP() DIV 16777216 or UNIX_TIMESTAMP() >> 24 for partitioning. That's 2 ^ 24. It's a little over 194 days. It's arbitrary. But if years are good partitioning breaks, so are these time periods. And, you're already using a bitfield model in your indexing.

Ok.. Thanks for your answer~ – user212942 Nov 30 '16 at 23:39 — user212942, Nov 30 '16 at 23:39

How to Get UTC Datetime from UNIX_TIMESTAMP() in MySQL

2 Answers2

Linked