generate sequence of dates in SQL for each variable

I have a table with three recipes and dates those recipes appear on the menu:

CREATE TABLE recipes (
  recipe_id INT NOT NULL,
  recipe_name VARCHAR(30) NOT NULL,
  recipe_date DATE,
  PRIMARY KEY (recipe_id)

INSERT INTO recipes 
    (recipe_id, recipe_name, recipe_date) 
    (1,'Tacos', to_date('2018-09-01', 'YYYY-MM-DD')),
    (2,'Tomato Soup', to_date('2018-10-01', 'YYYY-MM-DD')),
    (3,'Grilled Cheese', to_date('2018-11-01', 'YYYY-MM-DD')),
    (4,'Tacos', to_date('2018-09-03', 'YYYY-MM-DD')),
    (5,'Tomato Soup', to_date('2018-10-07', 'YYYY-MM-DD')),
    (6,'Grilled Cheese', to_date('2018-11-05', 'YYYY-MM-DD'));

My goal is to generate a series of dates that increments by one day for each recipe's min(recipe_date) until date(now()) and using that create a two-column table which contains the recipe_id and series of dates.

That means if today's date is 2018-11-07, there will be a total of 113 rows :

(68 days generated for Tacos, 38 days generated for Tomato Soup, and 7 days generated for Grilled Cheese)

How would one, in psql, generate a table containing a series of dates starting from the min for each recipe_id variable? Would this be with a CROSS JOIN ?

2 answers

  • answered 2018-11-07 23:11 LukStorms

    In PostgreSql you can actually get those dates via a Recursive CTE

    Test here on SQL Fiddle.

    with RECURSIVE RECIPEDATES (recipe_date, recipe_name) AS
      select min(recipe_date), recipe_name
      from recipes
      group by recipe_name
      union all
      select recipe_date + 1, recipe_name
      from RECIPEDATES
      where recipe_date <= current_date
    select recipe_name, recipe_date
    order by recipe_name, recipe_date;

  • answered 2018-11-08 03:25 Gordon Linoff

    In Postgres, you would use generate_series():

    select r.recipe_id, gs.dte
    from (select recipe_id, min(date) as mind, max(date) as maxd
          from recipes
          group by recipe_id
         ) r cross join lateral
         generate_series(mind, maxd, interval '1 day') as gs(dte)
    order by r.recipe_id, gs.dte;