CODEWITHSUNDEEP
postgresql
Bigboss
Bigboss

Reputation: 365

PostgreSQL Query given a set of date ranges

Table name: sample
   --Item--|---datefrom---|--dateto-- 
   Item A  |0001-01-01    |2099-01-01
   Item B  |2017-11-20    |2017-12-31
   Item C  |2017-11-27    |2017-12-12

Supposing we have the data above. How do I construct the query in such a way that I will get what is the current effective item given the date today.

Example. Since today is 2017-11-29 then I should get ITEM C.

I've tried this but I'm just wondering if there is a more effective query for this?

select * from sample where datefrom>= (select datefrom from sample where datefrom < '2017-11-29' order by datefrom desc limit 1 ) and dateto <= (select dateto from sample where dateto > '2017-11-29' order by dateto limit 1)

Upvotes: 0

Views: 33

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 520958

The following query will return the most recent item whose range overlaps the current date:

select *
from
(
    select *,
        row_number() over (order by abs(current_date - datefrom)) rn
    from sample
    where current_date between datefrom and dateto
) t
where rn = 1;

If two or more items happened to be tied for the latest, and you want all ties, then just replace row_number with rank.

But from what I see your Item A has a range which might also include today. I am not sure why its range starts from the year zero, or if that is even valid/makes sense.

As pointed out by @a_horse we could use the following simplified query if we are certain/don't care about ties between items for being the closest:

select *
from sample
where current_date between datefrom and dateto
order by abs(current_date - datefrom)
limit 1;

Upvotes: 1

Related Questions