PostgreSQL Query given a set of date ranges

Question

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)

why not just select * from sample where datefrom <= now() and dateto >=now() order by datefrom limit 1? — Vao Tsun
– Vao Tsun, Commented Nov 29, 2017 at 8:06
Unrelated, but: in Postgres you can use -infinity and infinity instead of those "magic dates". — user330315
– user330315, Commented Nov 29, 2017 at 8:17

Tim Biegeleisen · Accepted Answer · 2017-11-29 08:32:27Z

1

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;

edited Nov 29, 2017 at 8:32

answered Nov 29, 2017 at 8:05

Tim Biegeleisen

526k32 gold badges323 silver badges399 bronze badges

Sign up to request clarification or add additional context in comments.

9 Comments

Bigboss Over a year ago

it acts as a default value in case there is not row returned.

Tim Biegeleisen Over a year ago

What determines which item gets reported should there be two or more matches, which sounds like it is always the case?

Bigboss Over a year ago

It should return the row closest to the current date. E.G. between ITEM A and ITEM C. ITEM C should take precedence since its the closest.

Tim Biegeleisen Over a year ago

Which date defines "closest?" The to date or the from date?

user330315 Over a year ago

You don't need the window function. You can simply use order by abs(current_date - datefrom) limit 1 in your original version of the query.

|

Collectives™ on Stack Overflow

PostgreSQL Query given a set of date ranges

1 Answer 1

9 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

9 Comments

Your Answer

Sign up or log in

Post as a guest

Related