mysql - 使用 MySQL，为什么这个日期查询显示不正确的结果？

Question

样品表：

id | foreign_key_id | timestamp           | amt |
-------------------------------------------------
1  | 223344         | 2018-06-01 09:22:31 | 3
2  | 233445         | 2018-06-15 23:22:31 | 2
2  | 233445         | 2018-06-30 23:22:31 | 5
3  | 334455         | 2018-07-01 12:22:31 | 1
3  | 334455         | 2018-07-15 12:22:31 | 1
4  | 344556         | 2018-07-31 20:22:31 | 2

我想要的是amt每月总数的分组结果，例如，

year | month | total_amt
------------------------
2018 |     6 |       10
2018 |     7 |        4

我认为通过这样的查询很容易实现，

SELECT YEAR(timestamp) year, MONTH(timestamp) month, SUM(amt) total_amt
FROM sample_table
WHERE timestamp >= '2018-06-01'
AND timestamp <= '2018-07-31'
GROUP BY YEAR(timestamp), MONTH(timestamp)

不幸的是，这个查询的结果是不正确的，

year | month | total_amt
------------------------
2018 |     6 |       10
2018 |     7 |        2

六月的金额是正确的，但七月的金额是错误的。

标签： mysql

Answer 1

这是对这里什么是时间戳的误解，然后是比较字符串是什么。

在时间戳中是一个日期时间对象，它既有日期部分，也有时间部分。查询中使用的字符串只是日期部分，没有时间部分，所以当 MySQL 做它的事情时，它基本上“清零”了日期的其余部分。

所以当查询是

...
AND timestamp <= '2018-07-31'

它基本上变成了，

...
AND timestamp <= '2018-07-31 00:00:00'

当您转储与查询不匹配的行时，

...
AND '2018-07-31 20:22:31' <= '2018-07-31 00:00:00'

无论是日期比较，还是简单的字符串比较，缺失行的时间戳不小于或等于传入的日期，肯定是多。

你有几个选项来解决这个问题，在比较中创建一个完整的日期时间对象，“最完整”的时间，

...
AND timestamp <= '2018-07-31 23:59:59'

将运算符更改为小于下一个日期，

...
AND timestamp < '2018-08-01'

或者将时间戳从日期时间对象转换为日期对象，

...
AND DATE(timestamp) <= '2018-07-31'

所有的工作，虽然我不确定哪个是最好的性能/速度。