snippetsqlMinor

Convert date in date list condition to list of date ranges

Submitted by: @import:stackexchange-dba·Mar 10, 2026·

Viewed 0 times

conditionconvertdaterangeslist

Problem

I want to look for all the records that occur on specific dates.

SELECT *
FROM table1
WHERE date(column) in ($date1, $date2, ...);

However, as many of you, know this kind of comparison doesn't get along with indexes. So, I was wondering if there is a simple way to convert this query to something of the style of the following query without too much effort (i.e., : not using an external tool).

SELECT *
FROM table1
WHERE (column >= $date1 AND column = $date2 AND column < $date2 + interval 1 day)
   ...

So the optimizer can still use the indexes. (I'm using MySQL, but ANSI SQL would be great)

Solution

SUGGESTION #1

SELECT A.* FROM table1 A INNER JOIN
(
    SELECT '2015-03-01' dtcolumn
    UNION SELECT '2015-03-15'
    UNION SELECT '2015-04-01'
    UNION SELECT '2015-04-15'
    UNION SELECT '2015-05-01'
) B ON
A.dtcolumn >= B.dtcolumn
AND A.dtcolumn < B.dtcolumn + INTERVAL 1 DAY;

SUGGESTION #2

SELECT * FROM table1 WHERE
(column >= '2015-03-01' AND
column = '2015-03-15' AND
column = '2015-04-01' AND
column = '2015-04-15' AND
column = '2015-05-01' AND
column < '2015-05-01' + INTERVAL 1 DAY);

Code Snippets

SELECT A.* FROM table1 A INNER JOIN
(
    SELECT '2015-03-01' dtcolumn
    UNION SELECT '2015-03-15'
    UNION SELECT '2015-04-01'
    UNION SELECT '2015-04-15'
    UNION SELECT '2015-05-01'
) B ON
A.dtcolumn >= B.dtcolumn
AND A.dtcolumn < B.dtcolumn + INTERVAL 1 DAY;

SELECT * FROM table1 WHERE
(column >= '2015-03-01' AND
column < '2015-03-01' + INTERVAL 1 DAY)
UNION
SELECT * FROM table1 WHERE
(column >= '2015-03-15' AND
column < '2015-03-15' + INTERVAL 1 DAY)
UNION
SELECT * FROM table1 WHERE
(column >= '2015-04-01' AND
column < '2015-04-01' + INTERVAL 1 DAY)
UNION
SELECT * FROM table1 WHERE
(column >= '2015-04-15' AND
column < '2015-04-15' + INTERVAL 1 DAY)
UNION
SELECT * FROM table1 WHERE
(column >= '2015-05-01' AND
column < '2015-05-01' + INTERVAL 1 DAY);

Context

StackExchange Database Administrators Q#98796, answer score: 7

Revisions (0)

No revisions yet.