MySQL 日期时间比较似乎不正确

Question

以下是查询和输出

mysql> select q.id id,CONVERT_TZ( q.lastUpdatedAt,'+00:00',@@session.time_zone) lastUpdatedAt from Question q inner join QuestionInformation qi on q.id=qi.question_id;

+-----+---------------------+
| id  | lastUpdatedAt       |
+-----+---------------------+
| 206 | 2014-08-10 06:49:17 |
| 207 | 2014-08-10 06:49:59 |
| 208 | 2014-08-10 06:50:47 |
| 209 | 2014-08-10 06:51:04 |
| 210 | 2014-08-10 06:51:42 |
| 211 | 2014-08-10 06:52:10 |
| 212 | 2014-08-10 16:09:13 |
| 213 | 2014-08-10 16:12:04 |
| 214 | 2014-08-10 16:22:53 |
| 215 | 2014-08-10 16:23:44 |
| 216 | 2014-08-10 16:25:55 |
| 217 | 2014-08-10 16:46:39 |
+-----+---------------------+

12 rows in set (0.00 sec)
mysql> select q.id id,CONVERT_TZ( q.lastUpdatedAt,'+00:00',@@session.time_zone) lastUpdatedAt from Question q inner join QuestionInformation qi on q.id=qi.question_id where   lastUpdatedAt > '2014-08-10 11:10:07';

+-----+---------------------+
| id  | lastUpdatedAt       |
+-----+---------------------+
| 217 | 2014-08-10 16:46:39 |
+-----+---------------------+

1 row in set (0.01 sec)

mysql> 

但是有不止一行的最后更新时间大于2014-08-10 11:10:07。有人能指出我哪里出错了吗？

Answer 1

我怀疑问题在于您认为您在时区转换后获得了 lastUpdatedAt ，但事实并非如此。在查询中:

select q.id id, CONVERT_TZ(q.lastUpdatedAt,'+00:00',@@session.time_zone) as lastUpdatedAt
from Question q inner join
     QuestionInformation qi
     on q.id=qi.question_id
where lastUpdatedAt > '2014-08-10 11:10:07';

where 子句实际上是:

where q.lastUpdatedAt > '2014-08-10 11:10:07';

要解决此问题，您可以在 where 子句中重复表达式:

select q.id id,
       CONVERT_TZ(q.lastUpdatedAt,'+00:00',@@session.time_zone) as lastUpdatedAtTZ
from Question q inner join
     QuestionInformation qi
     on q.id=qi.question_id
where  CONVERT_TZ(q.lastUpdatedAt,'+00:00',@@session.time_zone) > '2014-08-10 11:10:07';

或者给它一个不同的别名并使用having子句:

select q.id id,
       CONVERT_TZ(q.lastUpdatedAt,'+00:00',@@session.time_zone) as lastUpdatedAtTZ
from Question q inner join
     QuestionInformation qi
     on q.id=qi.question_id
having lastUpdatedAtTZ > '2014-08-10 11:10:07';

后者是 MySQL 扩展。在其他数据库中，您将使用子查询或 CTE。