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
MySQL Select Using Multiple Date/Time Ranges
Moderator: General Moderators
Re: MySQL Select Using Multiple Date/Time Ranges
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
Re: MySQL Select Using Multiple Date/Time Ranges
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
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