169

I have a table which contains a datetime column. I wish to return all records of a given day regardless of the time. Or in other words, if my table only contained the following 4 records, then only the 2nd and 3rd would be returned if I limit to 2012-12-25.

2012-12-24 00:00:00
2012-12-25 00:00:00
2012-12-25 06:00:00
2012-12-26 05:00:00
Danny Beckett
  • 20,529
  • 24
  • 107
  • 134
user1032531
  • 24,767
  • 68
  • 217
  • 387
  • See answer by John Woo at http://stackoverflow.com/questions/14769026/mysql-select-where-timestamp-today – ekerner Aug 02 '15 at 11:50

5 Answers5

411

NEVER EVER use a selector like DATE(datecolumns) = '2012-12-24' - it is a performance killer:

  • 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

It is much faster to use

SELECT * FROM tablename 
WHERE columname BETWEEN '2012-12-25 00:00:00' AND '2012-12-25 23:59:59'

as this will allow index use without calculation.

EDIT

As pointed out by Used_By_Already, in the time since the inital answer in 2012, there have emerged versions of MySQL, where using '23:59:59' as a day end is no longer safe. An updated version should read

SELECT * FROM tablename 
WHERE columname >='2012-12-25 00:00:00'
AND columname <'2012-12-26 00:00:00'

The gist of the answer, i.e. the avoidance of a selector on a calculated expression, of course still stands.

smfoote
  • 5,449
  • 5
  • 32
  • 38
Eugen Rieck
  • 64,175
  • 10
  • 70
  • 92
  • I was about to comment that a1ex07's solution seems not near as good as the others. After reading your post, maybe I need to reverse my thinking! – user1032531 Dec 31 '12 at 17:26
  • 2
    Just tested on two servers the above is MUCH (10x at least) faster than date()='' especially for huge tables. Thanks – zzapper Aug 19 '14 at 13:28
  • Isn't it enough to say *and 2012-12-26*? The date is automatically assumed to be at the time 0 (i.e. 00:00:00.000) and the upper bound is exclusive. Or am I just being confused? – Konrad Viltersten Aug 27 '14 at 15:38
  • 1
    @KonradViltersten I used a very verbose way to phrase the query to make it more readable and drive home my point, a.o.t. refining it. The answer by a1ex07 has the sparse syntax. – Eugen Rieck Aug 27 '14 at 17:35
  • @EugenRieck what if I also needed to get the time in a different timezone? – majidarif Dec 17 '14 at 15:12
  • @majidarif Use `CONVERT_TZ()` on the constant arguments to care for the time offset. – Eugen Rieck Dec 19 '14 at 18:57
  • 17
    @KonradViltersten - Better yet: `WHERE col >= '2012-12-25' AND col < '2012-12-25' + INTERVAL 1 DAY`. It avoids 0 time and works for `DATE`, `DATETIME`, `DATETIME(6)`, etc. And deals with leap-day, etc. – Rick James Aug 28 '16 at 19:21
  • 4
    '2012-12-25 23:59:59' is NOT VALID as an end-of-day, it was always a bad idea and it will cause bugs for versions of MySQL that support sub-second time precision. Much better to use the approach by Rick James (above) or a1ex07 (other answer) – Paul Maxwell Nov 18 '16 at 09:31
  • @Used_By_Already You do realize, that at the time of the answer in 2012 there was no sub-second MySQL? – Eugen Rieck Nov 19 '16 at 11:43
  • Yes, I realise that, and I would have had the same feeling about this answer at that point (It is the same as Oracle's "date" data type). 23:59:59 is NOT the end of a "calendar day", never has been and never will be. The added problem is that these "venerated answers" can still get referred to without due consideration of relevance years later. – Paul Maxwell Nov 21 '16 at 00:56
  • what about `MONTH(datecolumn)` and `YEAR(datecolumn)`? are those performance killers too? – dapidmini Jan 12 '18 at 04:06
  • @dapidmini They are indeed. The important part is, that a calculation has to be applied to ALL values of that column in the table, rendering any index useless. – Eugen Rieck Jan 13 '18 at 18:26
  • @TusharBanne Do you mean 'NOW()' amd friends with "sysdate"? If yes, the performance is fine: MySQL evaluates those functions once, at the begin of the query, and then treats them as constants. – Eugen Rieck Nov 10 '18 at 16:21
  • Incase one is using java 8 to search for the date, you can use java 8 method: plusDay (long days )to increase the date from 25 to 26. Example: currentDay.plusDay(1L); – AfamO Mar 31 '22 at 10:13
42

... WHERE date_column >='2012-12-25' AND date_column <'2012-12-26' may potentially work better(if you have an index on date_column) than DATE.

a1ex07
  • 36,826
  • 12
  • 90
  • 103
  • 1
    I actually wonder what is faster - this or the `BETWEEN` solution ... anybody bechmarked? – jave.web Feb 17 '15 at 00:11
  • 6
    It shouldn't be any difference . `BETWEEN` is just a way to write `field >= value1 and fied<= value2`. – a1ex07 Feb 17 '15 at 00:16
  • 1
    Note that per the MySQL Manual: "For best results when using BETWEEN with date or time values, use CAST() to explicitly convert the values to the desired data type. Examples: If you compare a DATETIME to two DATE values, convert the DATE values to DATETIME values. If you use a string constant such as '2001-1-1' in a comparison to a DATE, cast the string to a DATE." So unless they are all the same type, it is best to explicitly cast them. – techdude Jun 24 '15 at 20:43
31

You can use %: 

SELECT * FROM datetable WHERE datecol LIKE '2012-12-25%'
Ghilas BELHADJ
  • 13,412
  • 10
  • 59
  • 99
3
SELECT * FROM `table` where Date(col) = 'date'
RF1991
  • 2,037
  • 4
  • 8
  • 17
Amar pratap singh
  • 187
  • 1
  • 5
0

Similiar to what Eugene Ricks said. If one is using spring data/jpa with Java 8 and above you can use plusDays(long hours) to increase day 25 to 26.

Example:

LocalDateTime lowerDateTime=LocalDateTime.parse("2012-12-25T00:00:00");
LocalDateTime upperDateTime = lowerDateTime.plusDays(1l);
System.out.println("my lowerDate =="+lowerDateTime);
System.out.println("my upperDate=="+upperDateTime);
AfamO
  • 849
  • 8
  • 7