How to transform this data in PostgreSQL

user_1943079 0
Viewed 113

How do I transform this data

Date1 Date2 ID
2021-01-01 2021-01-04 1
2021-01-05 2021-01-25 2

into this

Date ID
2021-01-01 1
2021-01-02 1
2021-01-03 1
2021-01-04 1
2021-01-05 2
2021-01-06 2
... ...
2021-01-24 2
2021-01-25 2

I'm working with Postgres and my intuition is telling me to use the generate_series function within Postgres but I'm not sure of the implementation. Any help appreciated.

common-table-expression date postgresql sql
3 Answers

You intuition is correct, and a calendar table approach should work here:

WITH dates AS (
    SELECT date_trunc('day', dt)::date AS dt
    FROM generate_series (
        '2021-01-01'::timestamp,
        '2021-01-31'::timestamp,
        '1 day'::interval) dt
)

SELECT d.dt AS Date, t.ID
FROM dates d
LEFT JOIN yourTable t
    ON d.dt BETWEEN t.Date1 AND t.Date2;

Demo

user_18632290

Since we might not know the boundary of the start and end dates in advance.

Here is an approach to do this

WITH yourTable AS (
    SELECT '2021-01-01'::date AS Date1, '2021-01-04'::date AS Date2, 1 AS ID 
    UNION ALL
    SELECT '2021-01-05'::date, '2021-01-25'::date, 2
)
select x.date1 + (lvl-1)*INTERVAL '1 day'
       ,x.id
  from yourTable x
  join generate_series(1,date2-date1+1) as lvl
    on 1=1

db fiddle
https://dbfiddle.uk/?rdbms=postgres_9.6&fiddle=3ef42eb613422c148b76561522d33a55


+---------------------+----+
|        date1        | id |
+---------------------+----+
| 2021-01-01 00:00:00 |  1 |
| 2021-01-02 00:00:00 |  1 |
| 2021-01-03 00:00:00 |  1 |
| 2021-01-04 00:00:00 |  1 |
| 2021-01-05 00:00:00 |  2 |
| 2021-01-06 00:00:00 |  2 |
| 2021-01-07 00:00:00 |  2 |
| 2021-01-08 00:00:00 |  2 |
| 2021-01-09 00:00:00 |  2 |
| 2021-01-10 00:00:00 |  2 |
| 2021-01-11 00:00:00 |  2 |
| 2021-01-12 00:00:00 |  2 |
| 2021-01-13 00:00:00 |  2 |
| 2021-01-14 00:00:00 |  2 |
| 2021-01-15 00:00:00 |  2 |
| 2021-01-16 00:00:00 |  2 |
| 2021-01-17 00:00:00 |  2 |
| 2021-01-18 00:00:00 |  2 |
| 2021-01-19 00:00:00 |  2 |
| 2021-01-20 00:00:00 |  2 |
| 2021-01-21 00:00:00 |  2 |
| 2021-01-22 00:00:00 |  2 |
| 2021-01-23 00:00:00 |  2 |
| 2021-01-24 00:00:00 |  2 |
| 2021-01-25 00:00:00 |  2 |
+---------------------+----+
user_71370900

I strongly recommend using a lateral join and generate_series():

select gs.date, t.id
from t cross join lateral
     generate_series(t.date1, t.date2, interval '1 day') gs(date);

Here is a db<>fiddle.

Why do I recommend this over a left join? Basically, the generate_series() operates on one row at time. So, the execution plan should basically be "read a row, generate the corresponding rows".

With a left join, Postgres has to actually match data across all rows, and that operation is much more expensive.

user_11440350
Related