I currently have a query for chart using date_trunc and the result is something like this
05/05/2022 - 1 06/05/2022 - 2 09/05/2022 - 8 10/05/2022 - 3
dates with no value are skipped , however I need it to be like this
05/05/2022 - 1 06/05/2022 - 2 07/05/2022 - 2 08/05/2022 - 2 09/05/2022 - 8 10/05/2022 - 3
notice how for 7th and 8th it will use the last available data which is in 6th. Will appreciate any help!
Advertisement
Answer
Use generate_series
to generate a row for each day, then select the last available data from your query result:
WITH query_result AS ( SELECT * FROM (VALUES ('2022-05-05'::timestamptz, 1), ('2022-05-06', 2), ('2022-05-09', 8), ('2022-05-10', 3) ) t (ts, value) ) SELECT d.day, (SELECT t.value from query_result t WHERE t.ts <= d.day ORDER BY t.ts desc limit 1) FROM generate_series('2022-05-05', '2022-05-10', '1 day'::interval) AS d(day) ORDER BY d.day;