如何从日期时间列选择日期?

我有一个类型为“datetime”的列，其值如2009-10-20 10:00:00

我想从datetime中提取date并写一个查询:

SELECT * FROM
data
WHERE datetime = '2009-10-20'
ORDER BY datetime DESC

以下是最好的方法吗?

SELECT * FROM
data
WHERE datetime BETWEEN('2009-10-20 00:00:00' AND '2009-10-20 23:59:59')
ORDER BY datetime DESC

然而，这将返回一个空结果集。有什么建议吗?

666485 次浏览
小开
最佳答案

你可以使用MySQL的DATE()函数:

WHERE DATE(datetime) = '2009-10-20'

你也可以试试这个:

WHERE datetime LIKE '2009-10-20%'

有关使用LIKE的性能影响的信息，请参阅这个答案。

小开

你可以使用:

DATEDIFF ( day , startdate , enddate ) = 0

或者:

DATEPART( day, startdate ) = DATEPART(day, enddate)
AND
DATEPART( month, startdate ) = DATEPART(month, enddate)
AND
DATEPART( year, startdate ) = DATEPART(year, enddate)

或者:

CONVERT(DATETIME,CONVERT(VARCHAR(12), startdate, 105)) = CONVERT(DATETIME,CONVERT(VARCHAR(12), enddate, 105))
小开
好吧，在语句中使用LIKE是最好的选择 WHERE datetime LIKE '2009-10-20%' 它应该在这种情况下工作

小开

您可以将datetime格式化为Y-M-D部分:

DATE_FORMAT(datetime, '%Y-%m-%d')
小开

简单和最好的方式使用日期函数

例子

SELECT * FROM
data
WHERE date(datetime) = '2009-10-20'

或

SELECT * FROM
data
WHERE date(datetime ) >=   '2009-10-20'  && date(datetime )  <= '2009-10-20'
小开

使用WHERE DATE(datetime) = '2009-10-20' 有性能问题。如上所述在这里:

  • 它将为所有行计算DATE()，包括那些不匹配的行。
  • 这将使查询不可能使用索引。

使用允许使用索引的BETWEEN或>， <， =操作符:

SELECT * FROM data
WHERE datetime BETWEEN '2009-10-20 00:00:00' AND '2009-10-20 23:59:59'

在索引列是高的中使用LIKE代替操作符影响。下面是一个包含1,176,000行的表的一些测试结果:

  • 使用datetime LIKE '2009-10-20%' => 2931ms
  • 使用datetime >= '2009-10-20 00:00:00' AND datetime <= '2009-10-20 23:59:59' => 168ms

当对同一个查询执行第二次调用时，差异甚至更大:2984ms vs 7ms(是的，只有7毫秒!)我是在用Hibernate重写一个项目的一些旧代码时发现的。

小开

尽管页面上的所有答案都将返回所需的结果，但它们都存在性能问题。永远不要对__ABC0子句中的字段执行转换(包括DATE()计算)，因为这种转换必须对表中的所有行执行。

BETWEEN ... AND构造包含这两种边界条件，要求在结束日期指定23:59:59语法，这本身有其他问题(微秒事务，我相信在2009年问这个问题时MySQL不支持)。

查询特定一天的MySQL timestamp字段的正确方法是根据所需的日期检查大于等于字段，并检查之后一天的小于字段，而不指定小时。

WHERE datetime>='2009-10-20' AND datetime<'2009-10-21'

这是执行速度最快、内存消耗最小、资源消耗最少的方法，而且还支持MySQL的所有特性和极端情况，比如亚秒级时间戳精度。此外，它是未来的证明。

小开

这里是所有的格式

假设这是包含datetime值的列，表data。

+--------------------+
| date_created       |
+--------------------+
| 2018-06-02 15:50:30|
+--------------------+

mysql> select DATE(date_created) from data;
+--------------------+
| DATE(date_created) |
+--------------------+
| 2018-06-02         |
+--------------------+

mysql> select YEAR(date_created) from data;
+--------------------+
| YEAR(date_created) |
+--------------------+
|               2018 |
+--------------------+

mysql> select MONTH(date_created) from data;
+---------------------+
| MONTH(date_created) |
+---------------------+
|                   6 |
+---------------------+

mysql> select DAY(date_created) from data;
+-------------------+
| DAY(date_created) |
+-------------------+
|                 2 |
+-------------------+

mysql> select HOUR(date_created) from data;
+--------------------+
| HOUR(date_created) |
+--------------------+
|                 15 |
+--------------------+

mysql> select MINUTE(date_created) from data;
+----------------------+
| MINUTE(date_created) |
+----------------------+
|                   50 |
+----------------------+

mysql> select SECOND(date_created) from data;
+----------------------+
| SECOND(date_created) |
+----------------------+
|                   31 |
+----------------------+
小开

我在一个大的唱片集上尝试了date(tscreated) = '2022-06-04'。我的tscreated被索引了。只用了42秒。

然后我尝试了tscreated >= '2022-06-04' and tscreated < '2022-06-05'，时间是0.094秒。

我意识到记录集第二次可能在内存中，但我也相信日期函数会对索引的值求反。


提交答案