MySQL - 从两个日期之间的数据库中选择数据
我已将用户注册的日期保存为日期时间,例如 2011-12-06 10:45:36.我已经运行了这个查询,我希望这个项目 - 2011-12-06 10:45:36 - 将被选中:
I have saved the dates of a user's registration as a datetime, so that's for instance 2011-12-06 10:45:36. I have run this query and I expected this item - 2011-12-06 10:45:36 - will be selected:
SELECT `users`.* FROM `users` WHERE created_at >= '2011-12-01' AND
created_at <= '2011-12-06'
但不是.存在任何优雅的方式,如何选择此项?我的第一个想法是 2011-12-06 + 1
,但这看起来不太好.
But is not. Exist any elegant way, how to select this item? As a first idea that I got was like 2011-12-06 + 1
, but this doesn't looks very nice.
推荐答案
您的问题是日期的短版本使用午夜作为默认值.所以你的查询实际上是:
Your problem is that the short version of dates uses midnight as the default. So your query is actually:
SELECT users.* FROM users
WHERE created_at >= '2011-12-01 00:00:00'
AND created_at <= '2011-12-06 00:00:00'
这就是为什么您没有看到 10:45 的记录.
This is why you aren't seeing the record for 10:45.
改为:
SELECT users.* FROM users
WHERE created_at >= '2011-12-01'
AND created_at <= '2011-12-07'
您也可以使用:
SELECT users.* from users
WHERE created_at >= '2011-12-01'
AND created_at <= date_add('2011-12-01', INTERVAL 7 DAY)
这将选择您要查找的同一时间间隔内的所有用户.
Which will select all users in the same interval you are looking for.
您可能还会发现 BETWEEN 运算符更具可读性:
You might also find the BETWEEN operator more readable:
SELECT users.* from users
WHERE created_at BETWEEN('2011-12-01', date_add('2011-12-01', INTERVAL 7 DAY));
相关文章