SQL: Getting all dates between a set of date pairs

Question

I have a table with some data and a time period i.e. start date and end date

     ------------------------------
    | id   | start_date | end_date |
    |------------------------------|
    | 0    | 1-1-2019   | 3-1-2019 |
    |------------------------------|
    | 1    | 6-1-2019   | 8-1-2019 |
    |------------------------------|

I want to run a query that will return the id and all the dates that are within those time periods. for instance, the result of the query for the above table will be:

   ------------------
  | id |    date     |
  |------------------|
  | 0  | 1-1-2019    |
  |------------------|
  | 0  | 2-1-2019    |
  |------------------|
  | 0  | 3-1-2019    |
  |------------------|
  | 1  | 6-1-2019    |
  |------------------|
  | 1  | 7-1-2019    |
  |------------------|
  | 1  | 8-1-2019    |
   ------------------

I am using Redshift therefor I need it supported in Postgres and take this into consideration

Your help will be greatly appriciated

Gordon Linoff · Answer 1 · 2019-07-16T13:19:13.193

0

This question was originally tagged Postgres.

Use generate_series():

select t.id, gs.dte
from t cross join lateral
     generate_series(t.start_date, t.end_date, interval '1 day') as gs(dte);

edited Jul 16 '19 at 13:19

answered Jul 16 '19 at 12:40

Gordon Linoff

1,242,037
58
646
786

I believe lateral was added in pg 9.3 redshift is based on pg 8.0.2 – Itay k Jul 16 '19 at 12:43
Redshift does not support table functions thus it does not support `generate_series()` – Jul 16 '19 at 12:45
@S-Man . . . The question was not tagged Redshift when I answered it. – Gordon Linoff Jul 16 '19 at 13:19

score 0 · Accepted Answer · answered Jul 16 '19 at 13:13

The common way this is done is to create a calendar table with a list of dates. In fact, a calendar table can be extended to include columns like:

Day number (in year)
Week number
First day of month
Last day of month
Weekday / Weekend
Public holiday

Simply create the table in Excel, save as CSV and then COPY it into Redshift.

You could then just JOIN to the table, like:

SELECT
  table.id,
  calendar.date
FROM table
JOIN calendar
WHERE
  calendar.date BETWEEN table.start_date AND table.end_date

Thanks, Took your idea but used a query to generate the calendar — Itay k, Jul 17 '19 at 12:19

score 0 · Answer 3 · answered Jul 17 '19 at 11:59

ok, It took me a while to get there but this is what I did (though not really proud of it): I created a query that generates a calendar for the last 6 years, cross joined it with my table and then selected the relevant dates from my calendar table.

WITH
days  AS (select 0 as num UNION select 1 as num UNION select 2 UNION select 3 UNION select 4 UNION select 5 UNION select 6 UNION select 7 UNION select 8 UNION select 9 UNION select 10 UNION select 11 UNION select 12 UNION select 13 UNION select 14 UNION select 15 UNION select 16 UNION select 17 UNION select 18 UNION select 19 UNION select 20 UNION select 21 UNION select 22 UNION select 23 UNION select 24 UNION select 25 UNION select 26 UNION select 27 UNION select 28 UNION select 29 UNION select 30 UNION select 31),
month    AS (select num from days  where num <= 12),
years     AS (select num from days where num <= 6),
rightnow AS (select CAST( TO_CHAR(GETDATE(), 'yyyy-mm-dd hh24') || ':' || trim(TO_CHAR((ROUND((DATEPART (MINUTE, GETDATE()) / 5), 1) * 5 ),'09')) AS TIMESTAMP) as start),
calendar as
(
select  
    DATEADD(years, -y.num, DATEADD( month, -m.num, DATEADD( days, -d.num, n.start ) ) ) AS period_date
from days d, month m, years y, rightnow n
)

select u.id, calendar.period_date
from periods u 
cross join calendar
where  date_part(DAY, u.finishedat) >= date_part(DAY, u.startedat) + 1 and date_part(DAY, calendar.period_date) < date_part(DAY, u.finishedat) and date_part(DAY, calendar.period_date) > date_part(DAY, u.startedat) and calendar.period_date < u.finishedat and calendar.period_date > u.startedat

This was based on the answer here: Using sql function generate_series() in redshift

SQL: Getting all dates between a set of date pairs

3 Answers3