I have a table with four columns. I want to create a date column with 1 day interval for each id
from its date1
till date3
. If no date3
then till date2
, if no date2
, then only date1
.
How can I achieve this in Postgres? Thanks!
Sample data:
+-------+---------------------+---------------------+---------------------+
| id | date1 | date2 | date3 |
+-------+---------------------+---------------------+---------------------+
| 76efg | 2021-01-03 06:33:54 | 2021-01-07 05:19:03 | 2021-01-08 05:19:03 |
| b67cs | 2021-01-09 03:45:24 | 2021-01-14 06:55:13 | |
| fsf56 | 2021-01-25 11:18:03 | 2021-01-25 11:18:03 | |
| ghl56 | 2021-01-29 14:25:57 | 2021-02-02 17:37:10 | 2021-02-18 01:13:37 |
| 90tum | 2021-02-18 06:13:30 | | |
+-------+---------------------+---------------------+---------------------+
Desired output for id '76efg':
+-------+--------------+---------------------+---------------------+---------------------+--+
| id | date_created | date1 | date2 | date3 | |
+-------+--------------+---------------------+---------------------+---------------------+--+
| 76efg | 2021-01-03 | 2021-01-03 06:33:54 | 2021-01-07 05:19:03 | 2021-01-08 05:19:03 | |
| 76efg | 2021-01-04 | 2021-01-03 06:33:54 | 2021-01-07 05:19:03 | 2021-01-08 05:19:03 | |
| 76efg | 2021-01-05 | 2021-01-03 06:33:54 | 2021-01-07 05:19:03 | 2021-01-08 05:19:03 | |
| 76efg | 2021-01-06 | 2021-01-03 06:33:54 | 2021-01-07 05:19:03 | 2021-01-08 05:19:03 | |
| 76efg | 2021-01-07 | 2021-01-03 06:33:54 | 2021-01-07 05:19:03 | 2021-01-08 05:19:03 | |
| 76efg | 2021-01-08 | 2021-01-03 06:33:54 | 2021-01-07 05:19:03 | 2021-01-08 05:19:03 | |
+-------+--------------+---------------------+---------------------+---------------------+--+
Have you tried using generated_series()
? The following query generates a series of timestamps based on date1
,date2
and date3
, and the limit of the series found using COALESCE
- basically checks if a column is null
.
WITH j (id,date1,date2,date3) AS (
VALUES ('76efg',
'2021-01-03 06:33:54'::TIMESTAMP,
'2021-01-07 05:19:03'::TIMESTAMP,
'2021-01-08 05:19:03'::TIMESTAMP ),
('b67cs',
'2021-01-09 03:45:24'::TIMESTAMP,
'2021-01-14 06:55:13'::TIMESTAMP,
NULL),
('90tum','2021-02-18 06:13:30'::TIMESTAMP,
NULL,NULL)
)
SELECT
id,
generate_series(
date1,
COALESCE(date3,date2,date1),'1 day'::INTERVAL) AS date_created,
date2,
date3
FROM j;
id | date_created | date2 | date3
-------+---------------------+---------------------+---------------------
76efg | 2021-01-03 06:33:54 | 2021-01-07 05:19:03 | 2021-01-08 05:19:03
76efg | 2021-01-04 06:33:54 | 2021-01-07 05:19:03 | 2021-01-08 05:19:03
76efg | 2021-01-05 06:33:54 | 2021-01-07 05:19:03 | 2021-01-08 05:19:03
76efg | 2021-01-06 06:33:54 | 2021-01-07 05:19:03 | 2021-01-08 05:19:03
76efg | 2021-01-07 06:33:54 | 2021-01-07 05:19:03 | 2021-01-08 05:19:03
b67cs | 2021-01-09 03:45:24 | 2021-01-14 06:55:13 |
b67cs | 2021-01-10 03:45:24 | 2021-01-14 06:55:13 |
b67cs | 2021-01-11 03:45:24 | 2021-01-14 06:55:13 |
b67cs | 2021-01-12 03:45:24 | 2021-01-14 06:55:13 |
b67cs | 2021-01-13 03:45:24 | 2021-01-14 06:55:13 |
b67cs | 2021-01-14 03:45:24 | 2021-01-14 06:55:13 |
90tum | 2021-02-18 06:13:30 | |
Check this db<>fiddle
.
Thanks @clamp for the hint regarding COALESCE
:
COALESCE(COALESCE(date3,date2),date1) is equivalent to COALESCE(date3,date2,date1)