Insert dates between Date Ranges in Postgresql

Question

I've been searching for quite a while and can't find an answer to this question:

I have a PostgreSQL table that is staged the following way:

Start Date | End Date   |  Name   | Team
-----------+------------+---------+------
2017-10-01 | 2017-10-10 | Person  |   1

And what I would like to is have each row a day between the start date and end date with the corresponding name and team of the person:

   Date     |  Name   |  Team
------------+---------+-------
2017-10-01  | Person  |   1
------------+---------+-------
2017-10-02  | Person  |   1
------------+---------+-------
2017-10-03  | Person  |   1

Is it even possible to do this with PostgreSQL? I'm currently running PostgreSQL 9.3.

user330315 · Accepted Answer · 2017-11-20 14:01:03Z

5

You can use generate_series() for that:

select t.dt::date, p.name, p.team
from person p, generate_series(p.start_date, p.end_date, interval '1' day) as t(dt)
order by t.dt::date;

I don't have 9.3 around any more, but I think that should also work with that old version.

answered Nov 20, 2017 at 14:01

user330315

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

Collectives™ on Stack Overflow

Insert dates between Date Ranges in Postgresql

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