CODEWITHSUNDEEP
phpmysqlsqldate
user1784397
user1784397

Reputation: 55

MySQL - Returning rows between two dates/times

The MySQL table I'm working with has date and time as separate columns. Dates are in the format of "y-m-d" and "hh:mm:ss" for time. How exactly do I search for the rows in between two times if they are in different days? (For example the rows between 2013-01-15 12:00:00 and 2013-01-17 17:00:00)

Right now, I'm using a bad workaround assuming that the time difference will be at most one day but that will not always be the case. There probably is an easy way of accomplishing this but I just can't figure out what. Thanks!

Upvotes: 0

Views: 2186

Answers (2)

dwurf
dwurf

Reputation: 12749

JW.'s answer can be sped up by first using the index to narrow down the search space and then trimming the results down to the correct set.

select * from births
where date between '2013-01-15' and '2013-01-17' -- use index
and concat(date, ' ', time) between '2013-01-15 12:00:00' and '2013-01-17 17:00:00' 
;

See this SQL Fiddle for more details.

Upvotes: 1

John Woo
John Woo

Reputation: 263693

concatenate the fields using CONCAT

SELECT  *
FROM    tableName
WHERE   CONCAT(dateCol, ' ', timeColumn) BETWEEN
            '2013-01-15 12:00:00' AND '2013-01-17 17:00:00'

but I recommend that you (if possible) to alter the table by combining the two columns with datatype DateTime so you can take advantage of the indexes. The query above which uses CONCAT requires full table scan which is very poor in performance for large databases.

Upvotes: 2

Related Questions