MySQL - Returning rows between two dates/times

Question

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!

Can you not alter the schema to combine the columns into a single DATETIME or TIMESTAMP type column? — eggyal
– eggyal, Commented Jan 16, 2013 at 8:03
Is it really that much faster that way? Changing the tables would take a really long time since lots of machines are writing to the database with this structure but I might ask for a change if it will really make a difference. — user1784397
– user1784397, Commented Jan 16, 2013 at 8:33

John Woo · Accepted Answer · 2013-01-16 08:03:11Z

2

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.

answered Jan 16, 2013 at 8:03

John Woo

265k70 gold badges509 silver badges500 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

dwurf · Accepted Answer · 2013-01-16 08:18:18Z

1

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.

answered Jan 16, 2013 at 8:18

dwurf

12.9k6 gold badges33 silver badges45 bronze badges

1 Comment

user1784397 Over a year ago

Awesome thanks to you both! The table I'm using is indeed very large and this indexing made it work reasonably.

Collectives™ on Stack Overflow

MySQL - Returning rows between two dates/times

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related