MySQL - 从两个日期之间的数据库中选择数据 [英] MySQL - select data from database between two dates

问题描述

我将用户注册的日期保存为日期时间，例如 2011-12-06 10:45:36 。我已经运行这个查询，我预计这个项目 - 2011-12-06 10:45:36 - 将被选中：

  SELECT`users`。* FROM`users` WHERE created_at> ='2011-12-01'AND 
 created_at< ='2011-12-06'
  

但不是。存在任何优雅的方式，如何选择这个项目？作为第一个想法，我得到了像$ code> 2011-12-06 + 1 ，但这看起来不是很好。

解决方案

您的问题是，短版本的日期使用午夜作为默认值。所以你的查询实际上是：

  SELECT users。* FROM users 
 WHERE created_at> ='2011-12- 01 00:00:00'
 AND created_at< ='2011-12-06 00:00:00'
  

这就是为什么你没有看到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）
  

哪些将在同一个间隔内选择所有用户。 / p>

您还可能会发现BETWEEN操作符更易读：

  SELECT用户。*从用户
 WHERE created_at BETWEEN（'2011-12-01'，date_add（'2011-12-01'，INTERVAL 7 DAY））; 
  

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'

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'

This is why you aren't seeing the record for 10:45.

Change it to:

SELECT users.* FROM users 
WHERE created_at >= '2011-12-01' 
AND created_at <= '2011-12-07'

You can also use:

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.

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));

这篇关于MySQL - 从两个日期之间的数据库中选择数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！