CODEWITHSUNDEEP
mysqlsqlselect
santa
santa

Reputation: 12512

Select results from yesterday and a week ago

I need to select results from my db that were stored yesterday and a week ago. Can I do it n the same query?

SELECT *
FROM t1
WHERE DATE(t1recordDate) = CURDATE() - INTERVAL 1 DAY

and

SELECT *
FROM t1
WHERE DATE(t1recordDate) = CURDATE() - INTERVAL 7 DAY

Upvotes: 0

Views: 192

Answers (3)

Paul Spiegel
Paul Spiegel

Reputation: 31812

If you don't like the trivial solution you can try this one:

select t1.*
from (
    select 
        curdate() - interval 1 day as ts_from,
        curdate() - interval 1 second as ts_to
    union all
    select 
        curdate() - interval 7 day as ts_from,
        curdate() - interval 6 day - interval 1 second as ts_to
) as r
join t1 on t1.t1recordDate between r.ts_from and r.ts_to

http://rextester.com/PLVMY63927

It can at least use an index.

Upvotes: 0

ScaisEdge
ScaisEdge

Reputation: 133380

You could use OR and date_sub if you need only the value for yesterday and a wekk ago but not for the days in range 

SELECT *
FROM t1
WHERE DATE(t1recordDate) = DATE(DATE_SUB(CURDATE(),INTERVAL 1 DAY))
OR DATE(t1recordDate) = DATE(DATE_SUB(CURDATE(),INTERVAL 7 DAY))

and if you need all the range 

SELECT *
FROM t1
WHERE DATE(t1recordDate) BETWEEN  DATE(DATE_SUB(CURDATE(),INTERVAL 7 DAY))
       AND  DATE(DATE_SUB(CURDATE(),INTERVAL 1 DAY))

Upvotes: 1

Mureinik
Mureinik

Reputation: 311823

You could use the or logical operator, or better yet, the shorthand in:

SELECT *
FROM   t1
WHERE  DATE(t1recordDate) IN 
       (CURDATE() - INTERVAL 1 DAY, CURDATE() - INTERVAL 7 DAY)

Upvotes: 1

Related Questions