CODEWITHSUNDEEP
postgresql
Anna Lindeberg
Anna Lindeberg

Reputation: 57

Sub-query in generate_series-function?

Long story short; I need to generate all dates between two given dates. My first instinct is just the generate_series function, so something like

\set start_date '''2019-10-01'''
\set end_date '''2019-11-07'''

SELECT current_date+AllDates.days
FROM generate_series(DATE :start_date-current_date,
DATE :end_date-current_date) AS AllDates(days);

which basically is some adapted code from the psql 9.5 documentation. This works, but I would like to get the start_date and end_date from the simple queries

SELECT min(checkout_date)
FROM table;

and

SELECT max(checkout_date)
FROM table;

How can I achieve this? Is there a better overall approach? I do realize the main problem with the two small queries is that they generate a table with the value and not the date-value in itself, but since I'm pretty new to SQL I have a hard time to grasp how one handles these situations.

Upvotes: 2

Views: 2086

Answers (1)

klin
klin

Reputation: 121919

with my_table(checkout_date) as (
values 
    ('2019-01-01'::date),
    ('2019-01-03'::date)    
)

select generate_series(min(checkout_date), max(checkout_date), interval '1 day')
from my_table;

    generate_series     
------------------------
 2019-01-01 00:00:00+01
 2019-01-02 00:00:00+01
 2019-01-03 00:00:00+01
(3 rows)

Note that you need the third interval parameter. Read about set returning functions in the documentation.

Upvotes: 1

Related Questions