How to select between dates Postgresql and list all those dates

Question

So, i have this table let's just call it product

============================================
sku  || valid_from ||  valid_to  || price ||
============================================
AAA  || 2021-01-26 || 2021-01-31 || 20000 ||
AAA  || 2021-02-01 || 2021-02-15 || 15000 ||
AAB  || 2021-02-27 || 2021-02-05 || 30000 ||
AAC  || 2021-01-28 || 2021-02-06 || 25000 ||
============================================

and i would like to select between a range of dates by code and list all the prices from the start to the end of the range like this:

(eg: select by sku = 'AAA', and from 2021-01-26 to 2021-02-02) expected output:

===============================
    date    || sku  || price ||
===============================
2021-01-26  || AAA  || 20000 ||
2021-01-27  || AAA  || 20000 ||
2021-01-28  || AAA  || 20000 ||
2021-01-29  || AAA  || 20000 ||
2021-01-30  || AAA  || 20000 ||
2021-01-31  || AAA  || 20000 ||
2021-02-01  || AAA  || 15000 ||
2021-02-02  || AAA  || 15000 ||
===============================

can anyone be of any help? doing a database query is definitely not my strong suit :/

user330315 · Accepted Answer · 2021-03-22 09:09:23Z

1

You can use generate_series() to "explode" the date range to a list of rows and then filter that on the dates you want:

select p.sku, g.dt::date, p.price
from product p
  cross join generate_series(p.valid_from, p.valid_to, interval '1 day') as g(dt)
where sku = 'AAA'
  and g.dt::date between date '2021-01-26' and date '2021-02-02'

This could be made more efficient by adding another WHERE condition that only selects the rows that actually fall into the desired range

  and daterange(valid_from, valid_to) && daterange(date '2021-01-26', date '2021-02-02', '[]')

For the given sample data it wouldn't make a difference though because all rows for the SKU AAA fall into that range

answered Mar 22, 2021 at 9:09

user330315

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

Collectives™ on Stack Overflow

How to select between dates Postgresql and list all those dates

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related