How to generate series between the min and max timestamps of two tables (Postgresql)

user_4238738 0
Viewed 116

I have two tables with weather data that are frequently updated. Table A has data with 10 min interval and table B with 1 hour interval.

Table A (realized weather)

observationtime temperature
17/02/21 00:00 9
17/02/21 00:10 9
17/02/21 00:20 9
17/02/21 00:30 9
... ...
17/02/21 03:00 9

Table B (weather forecast)

observationtime temperature
17/02/21 04:00 9
17/02/21 05:00 9
17/02/21 06:00 9
17/02/21 07:00 9

What I want

observationtime realized_temperature forecasted_temperature
17/02/21 00:00 9
17/02/21 01:00 9
17/02/21 02:00 9
17/02/21 03:00 9
17/02/21 04:00 9
17/02/21 05:00 9
17/02/21 06:00 9
17/02/21 07:00 9

So as far as I can gather three things need to happen:

  • First I need to get the smallest timestamp from table A and round it down the smallest whole hour
  • Get the max timestamp from the forecast table
  • Generate a series between these two timestamps with 1 hour intervals
  • Join Table A and B on the generated series

Can't quite figure out how to do this. Anyone have the solution?

generate-series postgresql
1 Answers

No need for generate_series() as I see it:

demo:db<>fiddle

SELECT
    COALESCE(r.observationtime, fc.observationtime) as observationtime,     -- 3
    r.temperature as realized_temperature,
    fc.temperature as forecasted_temperature
FROM (
    SELECT DISTINCT ON (date_trunc('hour', observationtime))                -- 1
        *
    FROM r
    ORDER BY date_trunc('hour', observationtime), observationtime
) r
FULL JOIN fc ON r.observationtime = fc.observationtime                      -- 2
ORDER BY 1
  1. First extract every first record per hour from the realized table. This can be done using DISTINCT ON which returns the first record of an ordered group. Here your group is the hour (with date_trunc() you can convert the hh:10 - hh:50 values to the full hour to join the group).
  2. Use a FULL JOIN: This joins the tables even if there are no matching timestamps
  3. Use COALESCE() to return the first non-NULL value in the list. So, if there is realized temperature, this will be taken, otherwise the forecasted.
user_39842210
Related