CODEWITHSUNDEEP
mysqldateselect
user1032531
user1032531

Reputation: 26321

MySQL SELECT WHERE datetime matches day (and not necessarily time)

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

Upvotes: 180

Views: 496291

Answers (5)

Amar pratap singh
Amar pratap singh

Reputation: 227

SELECT * FROM `table` where Date(col) = 'date'

Upvotes: 6

AfamO
AfamO

Reputation: 899

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

Upvotes: 0

Eugen Rieck
Eugen Rieck

Reputation: 65342

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.

Upvotes: 431

Ghilas BELHADJ
Ghilas BELHADJ

Reputation: 14124

You can use %: 

SELECT * FROM datetable WHERE datecol LIKE '2012-12-25%'

Upvotes: 31

a1ex07
a1ex07

Reputation: 37382

... 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.

Upvotes: 43

Related Questions