mysql: convert_tz 在 where 子句比较中被忽略？

Question

所以我有一个健康的每小时统计表，其中日期和时间是分开的，而不是保留为单一的日期时间:

+-------+-------------+------------+----------+
| cakes | pies        | day        | hour     |
+-------+-------------+------------+----------+
|     1 |          28 | 2012-02-21 |       20 |
|     0 |          14 | 2012-02-21 |       21 |
|     1 |          15 | 2012-02-21 |       22 |
|     1 |          11 | 2012-02-21 |       23 |
|     0 |           7 | 2012-02-22 |        0 |
|     0 |           9 | 2012-02-22 |        1 |
|     0 |           5 | 2012-02-22 |        2 |
|     0 |           8 | 2012-02-22 |        3 |
|     1 |          11 | 2012-02-22 |        4 |
|     0 |          11 | 2012-02-22 |        5 |
|     0 |          12 | 2012-02-22 |        6 |
|     1 |          19 | 2012-02-22 |        7 |
|     0 |          26 | 2012-02-22 |        8 |
|     0 |          20 | 2012-02-22 |        9 |
|     0 |          24 | 2012-02-22 |       10 |
|     2 |          26 | 2012-02-22 |       11 |
|     1 |          22 | 2012-02-22 |       12 |
|     1 |          24 | 2012-02-22 |       13 |
|     1 |          32 | 2012-02-22 |       14 |
|     0 |          25 | 2012-02-22 |       15 |
|     2 |          20 | 2012-02-22 |       16 |
|     0 |          24 | 2012-02-22 |       17 |
|     1 |          24 | 2012-02-22 |       18 |
|     0 |          15 | 2012-02-22 |       19 |
+-------+-------------+------------+----------+

我想从此表中进行时区敏感的选择，以便世界各地的人可以随时了解我的蛋糕或馅饼配额。

如果不进行时区转换，我的查询如下:

select cakes, pies, day, hour, 
    str_to_date(concat(day,' ',hour,':00'), '%Y-%m-%d %H:%i:%s') 'this' from stats where 
    str_to_date(concat(day,' ',hour,':00'), '%Y-%m-%d %H:%i:%s') between 
    str_to_date('2012-02-21 20:00:00', '%Y-%m-%d %H:%i:%s') and 
    str_to_date('2012-02-21 23:00:00', '%Y-%m-%d %H:%i:%s');    

..返回上面数据集的前四行:

+-------+-------------+------------+----------+---------------------+
| cakes | pies        | day        | hour     | this                |
+-------+-------------+------------+----------+---------------------+
|     1 |          28 | 2012-02-21 |       20 | 2012-02-21 20:00:00 |
|     0 |          14 | 2012-02-21 |       21 | 2012-02-21 21:00:00 |
|     1 |          15 | 2012-02-21 |       22 | 2012-02-21 22:00:00 |
|     1 |          11 | 2012-02-21 |       23 | 2012-02-21 23:00:00 |
+-------+-------------+------------+----------+---------------------+

到目前为止一切顺利。现在我需要让这个时区敏感。假设我的服务器在加利福尼亚，在夏令时期间有人在新西兰试图访问我从 2012-02-21 20:00:00 到 2012-02-21 23:00:00 的蛋糕和馅饼统计数据:

select cakes, pies, day, hour, 
    str_to_date(convert_tz(concat(day,' ',hour,':00'), '+13:00','-8:00'), '%Y-%m-%d %H:%i:%s') 'this' from stats where
    str_to_date(convert_tz(concat(day,' ',hour,':00'), '+13:00','-8:00'), '%Y-%m-%d %H:%i:%s') between 
    str_to_date(convert_tz('2012-02-21 20:00:00', '+13:00','-8:00') , '%Y-%m-%d %H:%i:%s') and 
    str_to_date(convert_tz('2012-02-21 23:00:00', '+13:00','-8:00') , '%Y-%m-%d %H:%i:%s');

但这就是它变得奇怪的地方:

+-------+-------------+------------+----------+---------------------+
| cakes | pies        | day        | hour     | this                |
+-------+-------------+------------+----------+---------------------+
|     1 |          28 | 2012-02-21 |       20 | 2012-02-20 23:00:00 |
|     0 |          14 | 2012-02-21 |       21 | 2012-02-21 00:00:00 |
|     1 |          15 | 2012-02-21 |       22 | 2012-02-21 01:00:00 |
|     1 |          11 | 2012-02-21 |       23 | 2012-02-21 02:00:00 |
+-------+-------------+------------+----------+---------------------+   

它似乎认为它正在查找时区调整值(“this”列)，但返回的统计数据与非时区查询的统计数据完全相同!这是怎么回事？

Answer 1

您正在使用 convert_tz 转换所有要比较的日期(输入日期和数据库中的日期)，因此您得到相同的数据也就不足为奇了。我想你可能想要的是

SELECT cakes, pies, day, hour, 
    CONVERT_TZ(CONCAT(day,' ',hour,':00'), '+13:00','-8:00') AS 'this' 
FROM stats 
WHERE 
    CONVERT_TZ(CONCAT(day,' ',hour,':00'), '+13:00','-8:00')
        BETWEEN 
            '2012-02-21 20:00:00' 
        AND '2012-02-21 23:00:00'

但是为什么要分开存储日期和时间呢？将它们存储在一起作为 DATETIME 字段将使您的查询更容易。此外，您不需要对已经是 MySQL DATETIME 值的值使用 STR_TO_DATE。