Page 1 of 1

MySQL Select Using Multiple Date/Time Ranges

Posted: Sun Jun 12, 2011 3:35 am
by dickey
Hello Forum,

I wish to query a mysql table and return a result set of records that match several non contiguous date/time ranges in a field of type datetime (field name = requestTime).

An example query might need to return records matching date / times:

between '2011-06-07 00:00:00' and '2011-06-07 21:00:00' and
between '2011-06-12 00:00:00' and '2011-06-12 23:59:59'...

I know I can achieve this by using the UNION of two SELECT STATEMENTS like:

SELECT * FROM visitors WHERE requestTime between '2011-06-07 00:00:00' and '2011-06-07 21:00:00' UNION SELECT * FROM visitors WHERE requestTime between '2011-06-12 00:00:00' and '2011-06-12 23:59:59'

...that being said my actual SELECT statements are more complex and if I can achieve this in a single statement I would be well pleased.

Is there a syntax for querying multiple date/time ranges in a single query or am I stuck with the union syntax. I have tried a range of syntax with little luck.

Your assistance is greatly appreciated.

Kind regards, Andrew

Re: MySQL Select Using Multiple Date/Time Ranges

Posted: Sun Jun 12, 2011 4:28 am
by VladSun
you dont need an UNION for this, simple OR in the WHERE clause would do the work:

Code: Select all

SELECT 
	* 
FROM 
	visitors 
WHERE 
	requestTime between '2011-06-07 00:00:00' and '2011-06-07 21:00:00' 
	OR
	requestTime between '2011-06-12 00:00:00' and '2011-06-12 23:59:59'

Re: MySQL Select Using Multiple Date/Time Ranges

Posted: Sun Jun 12, 2011 4:45 am
by dickey
Thanks VladSun,

Too obvious (ha ha), I had convinced myself I couldn't use between twice in the same select statement (feeling a bit silly now though). Thanks again.

Take care - Andrew