I have a database with time related information. I want a list with values for every minute. Like this:
12:00:00 3
12:01:00 4
12:02:00 5
12:03:00 5
12:04:00 5
12:05:00 3
But when there is no data for some minutes I got a result like this:
12:00:00 3
12:01:00 4
12:02:00 5
12:03:00 NULL
12:04:00 NULL
12:05:00 3
I want to fill the NULL value with the previous NOT NULL value.
This query creates a timeserie for every minute. Then it joins this to the data in my database.
I read something about window functions to fill the NULL value with the previous NOT NULL value, but I can't figure out how to implement this in this query. Can someone push me in the good direction?
I tried this solution, but the NULL values are still there: PostgreSQL use value from previous row if missing
This is my query:
SELECT
date,
close
FROM generate_series(
'2017-11-01 09:00'::timestamp,
'2017-11-01 23:59'::timestamp,
'1 minute') AS date
LEFT OUTER JOIN
(SELECT
date_trunc('minute', market_summary."timestamp") as day,
LAST(current, timestamp) AS close
FROM market_summary
WHERE created_at >= '2017-11-01 09:00'
AND created_at < '2017-11-01 23:59'
GROUP BY day
) results
ON (date = results.day)
ORDER BY date
See Question&Answers more detail:os