MySQL Select Using Multiple Date/Time Ranges

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
dickey
Forum Commoner
Posts: 50
Joined: Thu May 16, 2002 8:04 pm
Location: Sydney, Australia

MySQL Select Using Multiple Date/Time Ranges

Post 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
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Re: MySQL Select Using Multiple Date/Time Ranges

Post 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'
There are 10 types of people in this world, those who understand binary and those who don't
dickey
Forum Commoner
Posts: 50
Joined: Thu May 16, 2002 8:04 pm
Location: Sydney, Australia

Re: MySQL Select Using Multiple Date/Time Ranges

Post 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
Post Reply