MySQL 如何从日期时间列中选择日期？

How to select date from datetime column?

提问by mysqllearner

I have a column of type "datetime" with values like 2009-10-20 10:00:00

我有一个“日期时间”类型的列，其值类似于 2009-10-20 10:00:00

I would like to extract date from datetime and write a query like:

我想从日期时间中提取日期并编写如下查询：

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

Is the following the best way to do it?

以下是最好的方法吗？

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

This however returns an empty resultset. Any suggestions?

然而，这会返回一个空的结果集。有什么建议？

回答by Priyank Bolia

You can use MySQL's DATE()function:

您可以使用 MySQL 的DATE()功能：

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

You could also try this:

你也可以试试这个：

WHERE datetime LIKE '2009-10-20%'

WHERE datetime LIKE '2009-10-20%'

See this answerfor info on the performance implications of using LIKE.

见这个答案的使用对性能的影响的信息LIKE。

回答by IvanRF

Using WHERE DATE(datetime) = '2009-10-20'has performance issues. As stated here:

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

• it will calculate DATE()for all rows, including those that don't match.
• it will make it impossible to use an index for the query.

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

Use BETWEENor >, <, =operators which allow to use an index:

使用BETWEENor >, <,=允许使用索引的运算符：

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

---

---

Update:the impacton using LIKEinstead of operators in an indexed column is high. These are some test results on a table with 1,176,000 rows:

更新：在索引列中使用代替运算符的影响很大。以下是对包含 1,176,000 行的表的一些测试结果：LIKE

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

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

When doing a second call over the same query the difference is even higher: 2984ms vs 7ms (yes, just 7 milliseconds!). I found this while rewriting some old code on a project using Hibernate.

当对同一查询进行第二次调用时，差异甚至更大：2984 毫秒与 7 毫秒（是的，只有 7 毫秒！）。我在使用 Hibernate 在项目上重写一些旧代码时发现了这一点。

回答by Prusprus

You can format the datetime to the Y-M-D portion:

您可以将日期时间格式化为 YMD 部分：

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

回答by dotancohen

Though all the answers on the page will return the desired result, they all have performance issues. Never perform calculations on fields in the WHEREclause (including a DATE()calculation) as that calculation must be performed on all rows in the table.

尽管页面上的所有答案都会返回所需的结果，但它们都存在性能问题。切勿对子WHERE句中的字段执行计算（包括DATE()计算），因为必须对表中的所有行执行该计算。

The BETWEEN ... ANDconstruct is inclusive for both border conditions, requiring one to specify the 23:59:59 syntax on the end date which itself has other issues (microsecond transactions, which I believe MySQL did not support in 2009 when the question was asked).

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

The proper way to query a MySQL timestampfield for a particular day is to check for Greater-Than-Equals against the desired date, and Less-Than for the day after, with no hour specified.

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

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

This is the fastest-performing, lowest-memory, least-resource intensive method, and additionally supports all MySQL features and corner-cases such as sub-second timestamp precision. Additionally, it is future proof.

这是性能最快、内存最低、资源最少的方法，此外还支持所有 MySQL 功能和极端情况，例如亚秒级时间戳精度。此外，它是未来的证明。

回答by Siraj Alam

Here are all formats

这里是所有格式

Say this is the column that contains the datetimevalue, table data.

假设这是包含datetime值 table的列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 |
+----------------------+

回答by Michel van Engelen

You can use:

您可以使用：

DATEDIFF ( day , startdate , enddate ) = 0

Or:

或者：

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

Or:

或者：

CONVERT(DATETIME,CONVERT(VARCHAR(12), startdate, 105)) = CONVERT(DATETIME,CONVERT(VARCHAR(12), enddate, 105))

回答by Raja Rama Mohan Thavalam

simple and best way to use date function

使用日期函数的简单和最佳方法

example

例子

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

OR

或者

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

回答by imran

Well, using LIKEin statement is the best option WHERE datetime LIKE '2009-10-20%'it should work in this case

好吧， 在这种情况下，使用LIKEin 语句是最好的选择 WHERE datetime LIKE '2009-10-20%'