Postgres 13

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE r
    ("observationtime" timestamp, "temperature" int)
;
    
INSERT INTO r VALUES
    ('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 00:40', '9'),
    ('17/02/21 00:50', '9'),
    ('17/02/21 01:00', '9'),
    ('17/02/21 01:10', '9'),
    ('17/02/21 01:20', '9'),
    ('17/02/21 01:30', '9'),
    ('17/02/21 01:40', '9'),
    ('17/02/21 01:50', '9'),
    ('17/02/21 02:00', '9'),
    ('17/02/21 02:10', '9'),
    ('17/02/21 02:20', '9'),
    ('17/02/21 02:30', '9'),
    ('17/02/21 02:40', '9'),
    ('17/02/21 02:50', '9'),
    ('17/02/21 03:00', '9')
;

SELECT * FROM r
19 rows affected



observationtime
temperature


2021-02-17 00:00:00
9

2021-02-17 00:10:00
9

2021-02-17 00:20:00
9

2021-02-17 00:30:00
9

2021-02-17 00:40:00
9

2021-02-17 00:50:00
9

2021-02-17 01:00:00
9

2021-02-17 01:10:00
9

2021-02-17 01:20:00
9

2021-02-17 01:30:00
9

2021-02-17 01:40:00
9

2021-02-17 01:50:00
9

2021-02-17 02:00:00
9

2021-02-17 02:10:00
9

2021-02-17 02:20:00
9

2021-02-17 02:30:00
9

2021-02-17 02:40:00
9

2021-02-17 02:50:00
9

2021-02-17 03:00:00
9


CREATE TABLE fc
    ("observationtime" timestamp, "temperature" int)
;
    
INSERT INTO fc VALUES
    ('17/02/21 04:00', 9),
    ('17/02/21 05:00', 9),
    ('17/02/21 06:00', 9),
    ('17/02/21 07:00', 9)
;

SELECT * FROM fc
4 rows affected



observationtime
temperature


2021-02-17 04:00:00
9

2021-02-17 05:00:00
9

2021-02-17 06:00:00
9

2021-02-17 07:00:00
9


    SELECT DISTINCT ON (date_trunc('hour', observationtime))
        *
    FROM r
    ORDER BY date_trunc('hour', observationtime), observationtime


observationtime
temperature


2021-02-17 00:00:00
9

2021-02-17 01:00:00
9

2021-02-17 02:00:00
9

2021-02-17 03:00:00
9


SELECT
    *
FROM (
    SELECT DISTINCT ON (date_trunc('hour', observationtime))
        *
    FROM r
    ORDER BY date_trunc('hour', observationtime), observationtime
) r
FULL JOIN fc ON r.observationtime = fc.observationtime


observationtime
temperature
observationtime
temperature


null
null
2021-02-17 04:00:00
9

null
null
2021-02-17 05:00:00
9

null
null
2021-02-17 06:00:00
9

null
null
2021-02-17 07:00:00
9

2021-02-17 03:00:00
9
null
null

2021-02-17 02:00:00
9
null
null

2021-02-17 00:00:00
9
null
null

2021-02-17 01:00:00
9
null
null


SELECT
    COALESCE(r.observationtime, fc.observationtime) as observationtime,
    r.temperature as realized_temperature,
    fc.temperature as forecasted_temperature
FROM (
    SELECT DISTINCT ON (date_trunc('hour', observationtime))
        *
    FROM r
    ORDER BY date_trunc('hour', observationtime), observationtime
) r
FULL JOIN fc ON r.observationtime = fc.observationtime
ORDER BY 1


observationtime
realized_temperature
forecasted_temperature


2021-02-17 00:00:00
9
null

2021-02-17 01:00:00
9
null

2021-02-17 02:00:00
9
null

2021-02-17 03:00:00
9
null

2021-02-17 04:00:00
null
9

2021-02-17 05:00:00
null
9

2021-02-17 06:00:00
null
9

2021-02-17 07:00:00
null
9

observationtime	temperature	observationtime	temperature
null	null	2021-02-17 04:00:00	9
null	null	2021-02-17 05:00:00	9
null	null	2021-02-17 06:00:00	9
null	null	2021-02-17 07:00:00	9
2021-02-17 03:00:00	9	null	null
2021-02-17 02:00:00	9	null	null
2021-02-17 00:00:00	9	null	null
2021-02-17 01:00:00	9	null	null

observationtime	temperature
2021-02-17 00:00:00	9
2021-02-17 00:10:00	9
2021-02-17 00:20:00	9
2021-02-17 00:30:00	9
2021-02-17 00:40:00	9
2021-02-17 00:50:00	9
2021-02-17 01:00:00	9
2021-02-17 01:10:00	9
2021-02-17 01:20:00	9
2021-02-17 01:30:00	9
2021-02-17 01:40:00	9
2021-02-17 01:50:00	9
2021-02-17 02:00:00	9
2021-02-17 02:10:00	9
2021-02-17 02:20:00	9
2021-02-17 02:30:00	9
2021-02-17 02:40:00	9
2021-02-17 02:50:00	9
2021-02-17 03:00:00	9