fill in missing dates of date_trunc with last row data-CodePudding

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!

CodePudding user response：

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;