generate sequence of dates in SQL for each variable

Question

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) 
VALUES 
    (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 ?

mysqli -> MySQL, psql -> PostgreSQL. Which is it? This is considerably easier in PostgreSQL, BTW. — Clockwork-Muse
– Clockwork-Muse, Commented Nov 7, 2018 at 22:38
I'm just confused about to do this for each type of query in psql using a generate date sequence — iskandarblue
– iskandarblue, Commented Nov 7, 2018 at 22:45
Possible duplicate of how to insert a date series in postgreSQL — Clockwork-Muse
– Clockwork-Muse, Commented Nov 8, 2018 at 1:44

LukStorms · Accepted Answer · 2018-11-07 23:11:39Z

1

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
from RECIPEDATES
order by recipe_name, recipe_date;

answered Nov 7, 2018 at 23:11

LukStorms

29.8k5 gold badges36 silver badges49 bronze badges

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

Comments

Gordon Linoff · Accepted Answer · 2018-11-08 03:25:57Z

1

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;

answered Nov 8, 2018 at 3:25

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Collectives™ on Stack Overflow

generate sequence of dates in SQL for each variable

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related