Mam kolumnę typu „datetime” o wartościach takich jak 2009-10-20 10:00:00

Chciałbym wyodrębnić datę z daty i godziny i napisać zapytanie takie jak:

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

Czy najlepszy sposób to zrobić?

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

Zwraca to jednak pusty zestaw wyników. Jakieś sugestie?

Możesz użyć DATE()funkcji MySQL :

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

Możesz także spróbować tego:

WHERE datetime LIKE '2009-10-20%'

Zobacz tę odpowiedź, aby uzyskać informacje na temat wpływu używania na wydajność LIKE.

Korzystanie WHERE DATE(datetime) = '2009-10-20' ma problemy z wydajnością . Jak stwierdzono tutaj :

• obliczy DATE()wszystkie wiersze, w tym te, które nie pasują.
• uniemożliwi to użycie indeksu dla zapytania.

Użyj BETWEENlub >, <, =operatorów, które pozwalają korzystać z indeksu:

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

Aktualizacja: wpływ na wykorzystaniu LIKEzamiast operatorów w kolumnie indeksowanej jest wysoka . Oto niektóre wyniki testu dla tabeli zawierającej 1 176 000 wierszy:

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

Przy drugim wywołaniu tego samego zapytania różnica jest jeszcze większa: 2984ms vs 7ms (tak, tylko 7 milisekund!). Znalazłem to podczas przepisywania starego kodu w projekcie przy użyciu Hibernacji.

Możesz sformatować datę / godzinę do części YMD:

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

Chociaż wszystkie odpowiedzi na stronie zwrócą pożądany wynik, wszystkie mają problemy z wydajnością. Nigdy nie wykonuj obliczeń na polach w WHEREklauzuli (w tym DATE()obliczeń), ponieważ obliczenia te należy wykonać dla wszystkich wierszy w tabeli.

BETWEEN ... ANDKonstrukt jest łącznie dla obu warunków granicznych, wymagające jednego określić składnię 23:59:59 datą końcową, która sama ma inne problemy (transakcje mikrosekund, co wierzę MySQL nie wspiera w 2009 roku, kiedy pytano).

Właściwym sposobem zapytania o timestamppole MySQL dla określonego dnia jest sprawdzenie, czy wartość „Większa niż równa się” z żądaną datą, czy „mniejsza niż” dla następnego dnia, bez określonej godziny.

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

Jest to najszybsza metoda z najmniejszą ilością pamięci i najmniejszym zużyciem zasobów, a ponadto obsługuje wszystkie funkcje MySQL i przypadki narożne, takie jak precyzja znaczników czasu w sekundach. Dodatkowo jest to zabezpieczenie na przyszłość.

Oto wszystkie formaty

Powiedzmy, że jest to kolumna zawierająca datetimewartość, tabela 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 |
+----------------------+

Możesz użyć:

DATEDIFF ( day , startdate , enddate ) = 0

Lub:

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

Lub:

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

prosty i najlepszy sposób na użycie funkcji daty

przykład

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

LUB

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

Cóż, użycie LIKEinstrukcji in jest najlepszą opcją, WHERE datetime LIKE '2009-10-20%' która powinna działać w tym przypadku