mysql获得划时代的时间

Question

我用的桌子是：

CREATE TABLE tv (
    datetime datetime NOT NULL,
    value int(4),
    metric varchar(25),
    PRIMARY KEY (datetime)
);

我的系统在CET中，所以我从一个选择：

select * from tv;
+---------------------+-------+----------+
| datetime            | value | metric   |
+---------------------+-------+----------+
| 2017-08-09 14:17:27 |     0 | TV power |
| 2017-08-09 14:20:04 |     0 | TV power |
| 2017-08-09 14:40:04 |     0 | TV power |
| 2017-08-09 14:45:03 |     0 | TV power |

当我试图绘制它时(在grafana中)，我使用：

SELECT
  UNIX_TIMESTAMP(datetime) as time_sec,
  value,
  metric
FROM tv
ORDER BY datetime

而且它工作得很好，只是它在将来多写两个小时的东西。所以我猜这是因为UNIX_TIMESTAMP认为datetime是以UTC为单位的，并且查看系统值，因为它是CET，它在转换到纪元或类似的东西之前做了一个+02:00。

我也明白了：

SELECT @@global.time_zone;
+--------------------+
| @@global.time_zone |
+--------------------+
| SYSTEM             |
+--------------------+
1 row in set (0.00 sec)

这意味着它使用的是系统时区，即CET。

如何使我的UNIX_TIMESTAMP正确地转换为CET？

谢谢。

Answer 1

以下是文档对UNIX_TIMESTAMP的看法

如果使用date参数调用UNIX_TIMESTAMP()，它会将参数的值作为秒返回，自'1970-01-01 00:00:00‘UTC开始。

据我所知，MySQL正在将输入时间戳转换为UTC时间，进行计算，然后以UTC时间返回偏移量。对此进行更正的一个选择是在调用UNIX_TIMESTAMP之前将时间戳从"UTC“时间转换为"CET”时间。我在这里使用引号，因为您的时间戳已经在所需的CET时区，但是我们不得不欺骗MySQL，抵制它自己的努力，将一切都转换为UTC。

这样的东西应该能起作用：

SELECT
    UNIX_TIMESTAMP(CONVERT_TZ(datetime, 'UTC', 'CET')) AS time_sec,
    value,
    metric
FROM tv
ORDER BY datetime

再一次，我们将您的时间戳从UTC移到CET时间，以抵消UNIX_TIMESTAMP()的影响，这将与之相反。

如果上面对CONVERT_TZ()的调用不适用于您，您可能需要花费一些时间来配置MySQL时区表。下面是一个让你开始的问题：

数据库返回QuerySet.dates()中的无效值

作为最后的评论，我认为对你来说最好的长期解决方案就是把你的时间戳存储在UTC时间。然后，您只需要对传入日期和传出日期进行转换，而不需要对内部MySQL计算进行转换。以UTC时间存储所有时间戳信息是一种常见的数据库实践。