FREE AI-Enhanced Online PostgreSQL Compiler

Untitled*

You must execute the code again to save those values in the database.

-- INIT database
CREATE TABLE temp_export (
    site_name        text,
    date_time_utc    TIMESTAMP WITH TIME ZONE NOT NULL,
    precip_since_9am numeric,
    precip_10min     numeric,
    PRIMARY KEY (site_name, date_time_utc)
);
INSERT INTO temp_export (
    site_name, date_time_utc, precip_since_9am, precip_10min
)
VALUES ('sitea', '2024-01-18 06:00:00.000000 +00:00', 10.8, 0.2), ('sitea', '2024-01-18 05:50:00.000000 +00:00', 10.6, 0.4), ('sitea', '2024-01-18 05:40:00.000000 +00:00', 10.2, 0.2), ('sitea', '2024-01-18 05:30:00.000000 +00:00', 10, 0.2), ('sitea', '2024-01-18 05:20:00.000000 +00:00', 9.8, 0.4), ('sitea', '2024-01-18 05:10:00.000000 +00:00', 9.4, 0.2), ('sitea', '2024-01-18 05:00:00.000000 +00:00', 9.2, 0.2), ('sitea', '2024-01-18 04:50:00.000000 +00:00', 9, 0.2), ('sitea', '2024-01-18 04:40:00.000000 +00:00', 8.8, 0.2), ('sitea', '2024-01-18 04:30:00.000000 +00:00', 8.6, 0.4), ('sitea', '2024-01-18 04:20:00.000000 +00:00', 8.2, 0.4), ('sitea', '2024-01-18 04:10:00.000000 +00:00', 7.8, 0.2), ('sitea', '2024-01-18 04:00:00.000000 +00:00', 7.6, 0.2), ('sitea', '2024-01-18 03:50:00.000000 +00:00', 7.4, 0.2), ('sitea', '2024-01-18 03:40:00.000000 +00:00', 7.2, 0), ('sitea', '2024-01-18 03:30:00.000000 +00:00', 7.2, 0.4), ('sitea', '2024-01-18 03:20:00.000000 +00:00', 6.8, 0), ('sitea', '2024-01-18 03:10:00.000000 +00:00', 6.8, 0), ('sitea', '2024-01-18 03:00:00.000000 +00:00', 6.8, 0.2), ('sitea', '2024-01-18 02:50:00.000000 +00:00', 6.6, 0), ('sitea', '2024-01-18 02:40:00.000000 +00:00', 6.6, 0.2), ('sitea', '2024-01-18 02:30:00.000000 +00:00', 6.4, 0.2), ('sitea', '2024-01-18 02:20:00.000000 +00:00', 6.2, 0.4), ('sitea', '2024-01-18 02:10:00.000000 +00:00', 5.8, 0.6), ('sitea', '2024-01-18 02:00:00.000000 +00:00', 5.2, 0.6), ('sitea', '2024-01-18 01:50:00.000000 +00:00', 4.6, 0.6), ('sitea', '2024-01-18 01:40:00.000000 +00:00', 4, 0.4), ('sitea', '2024-01-18 01:30:00.000000 +00:00', 3.6, 0.2), ('sitea', '2024-01-18 01:20:00.000000 +00:00', 3.4, 0.2), ('sitea', '2024-01-18 01:10:00.000000 +00:00', 3.2, 0), ('sitea', '2024-01-18 01:00:00.000000 +00:00', 3.2, 0.2), ('sitea', '2024-01-18 00:50:00.000000 +00:00', 3, 0.2), ('sitea', '2024-01-18 00:40:00.000000 +00:00', 2.8, 0.2), ('sitea', '2024-01-18 00:30:00.000000 +00:00', 2.6, 0.2), ('sitea', '2024-01-18 00:20:00.000000 +00:00', 2.4, 0), ('sitea', '2024-01-18 00:10:00.000000 +00:00', 2.4, 0), ('sitea', '2024-01-18 00:00:00.000000 +00:00', 2.4, 0.2), ('sitea', '2024-01-17 23:50:00.000000 +00:00', 2.2, 0), ('sitea', '2024-01-17 23:40:00.000000 +00:00', 2.2, 0), ('sitea', '2024-01-17 23:30:00.000000 +00:00', 2.2, 0), ('sitea', '2024-01-17 23:20:00.000000 +00:00', 2.2, 0), ('sitea', '2024-01-17 23:10:00.000000 +00:00', 2.2, 0), ('sitea', '2024-01-17 23:00:00.000000 +00:00', 2.2, 0), ('sitea', '2024-01-17 22:50:00.000000 +00:00', 2.2, 0.2), ('sitea', '2024-01-17 22:40:00.000000 +00:00', 2, 0.6), ('sitea', '2024-01-17 22:30:00.000000 +00:00', 1.4, 0.6), ('sitea', '2024-01-17 22:20:00.000000 +00:00', 0.8, 0.6), ('sitea', '2024-01-17 22:10:00.000000 +00:00', 0.2, 0.2), ('sitea', '2024-01-17 22:00:00.000000 +00:00', 16.8, 0), ('sitea', '2024-01-17 21:50:00.000000 +00:00', 16.8, 0.2), ('sitea', '2024-01-17 21:40:00.000000 +00:00', 16.6, 0), ('sitea', '2024-01-17 21:30:00.000000 +00:00', 16.6, 0.2), ('sitea', '2024-01-17 21:20:00.000000 +00:00', 16.4, 0), ('sitea', '2024-01-17 21:10:00.000000 +00:00', 16.4, 0.2), ('sitea', '2024-01-17 21:00:00.000000 +00:00', 16.2, 0.2), ('sitea', '2024-01-17 20:50:00.000000 +00:00', 16, 1), ('sitea', '2024-01-17 20:40:00.000000 +00:00', 15, 0.6), ('sitea', '2024-01-17 20:30:00.000000 +00:00', 14.4, 0.4), ('sitea', '2024-01-17 20:20:00.000000 +00:00', 14, 0.2), ('sitea', '2024-01-17 20:10:00.000000 +00:00', 13.8, 0), ('sitea', '2024-01-17 20:00:00.000000 +00:00', 13.8, 0), ('sitea', '2024-01-17 19:50:00.000000 +00:00', 13.8, 0), ('sitea', '2024-01-17 19:40:00.000000 +00:00', 13.8, 0.4), ('sitea', '2024-01-17 19:30:00.000000 +00:00', 13.4, 0.6), ('sitea', '2024-01-17 19:20:00.000000 +00:00', 12.8, 0.2), ('sitea', '2024-01-17 19:10:00.000000 +00:00', 12.6, 0), ('sitea', '2024-01-17 19:00:00.000000 +00:00', 12.6, 0), ('sitea', '2024-01-17 18:50:00.000000 +00:00', 12.6, 0), ('sitea', '2024-01-17 18:40:00.000000 +00:00', 12.6, 0), ('sitea', '2024-01-17 18:30:00.000000 +00:00', 12.6, 0.2), ('sitea', '2024-01-17 18:20:00.000000 +00:00', 12.4, 0.2), ('sitea', '2024-01-17 18:10:00.000000 +00:00', 12.2, 0.2), ('sitea', '2024-01-17 18:00:00.000000 +00:00', 12, 0), ('sitea', '2024-01-17 17:50:00.000000 +00:00', 12, 0), ('sitea', '2024-01-17 17:40:00.000000 +00:00', 12, 0), ('sitea', '2024-01-17 17:30:00.000000 +00:00', 12, 0), ('sitea', '2024-01-17 17:20:00.000000 +00:00', 12, 0), ('sitea', '2024-01-17 17:10:00.000000 +00:00', 12, 0), ('sitea', '2024-01-17 17:00:00.000000 +00:00', 12, 0), ('sitea', '2024-01-17 16:50:00.000000 +00:00', 12, 0.4), ('sitea', '2024-01-17 16:40:00.000000 +00:00', 11.6, 0.2), ('sitea', '2024-01-17 16:30:00.000000 +00:00', 11.4, 0), ('sitea', '2024-01-17 16:20:00.000000 +00:00', 11.4, 0), ('sitea', '2024-01-17 16:10:00.000000 +00:00', 11.4, 0), ('sitea', '2024-01-17 16:00:00.000000 +00:00', 11.4, 0), ('sitea', '2024-01-17 15:50:00.000000 +00:00', 11.4, 0.6), ('sitea', '2024-01-17 15:40:00.000000 +00:00', 10.8, 0.2), ('sitea', '2024-01-17 15:30:00.000000 +00:00', 10.6, 0.2), ('sitea', '2024-01-17 15:20:00.000000 +00:00', 10.4, 0.4), ('sitea', '2024-01-17 15:10:00.000000 +00:00', 10, 0.2), ('sitea', '2024-01-17 15:00:00.000000 +00:00', 9.8, 0), ('sitea', '2024-01-17 14:50:00.000000 +00:00', 9.8, 0), ('sitea', '2024-01-17 14:40:00.000000 +00:00', 9.8, 0), ('sitea', '2024-01-17 14:30:00.000000 +00:00', 9.8, 0), ('sitea', '2024-01-17 14:20:00.000000 +00:00', 9.8, 0.2), ('sitea', '2024-01-17 14:10:00.000000 +00:00', 9.6, 0), ('sitea', '2024-01-17 14:00:00.000000 +00:00', 9.6, 0), ('sitea', '2024-01-17 13:50:00.000000 +00:00', 9.6, 0), ('sitea', '2024-01-17 13:40:00.000000 +00:00', 9.6, 0), ('sitea', '2024-01-17 13:30:00.000000 +00:00', 9.6, 0), ('sitea', '2024-01-17 13:20:00.000000 +00:00', 9.6, 0), ('sitea', '2024-01-17 13:10:00.000000 +00:00', 9.6, 0.8), ('sitea', '2024-01-17 13:00:00.000000 +00:00', 8.8, 0.6), ('sitea', '2024-01-17 12:50:00.000000 +00:00', 8.2, 0), ('sitea', '2024-01-17 12:40:00.000000 +00:00', 8.2, 0), ('sitea', '2024-01-17 12:30:00.000000 +00:00', 8.2, 0), ('sitea', '2024-01-17 12:20:00.000000 +00:00', 8.2, 0), ('sitea', '2024-01-17 12:10:00.000000 +00:00', 8.2, 0), ('sitea', '2024-01-17 12:00:00.000000 +00:00', 8.2, 0.4), ('sitea', '2024-01-17 11:50:00.000000 +00:00', 7.8, 0), ('sitea', '2024-01-17 11:40:00.000000 +00:00', 7.8, 0), ('sitea', '2024-01-17 11:30:00.000000 +00:00', 7.8, 0), ('sitea', '2024-01-17 11:20:00.000000 +00:00', 7.8, 0.4), ('sitea', '2024-01-17 11:10:00.000000 +00:00', 7.4, 0.8), ('sitea', '2024-01-17 11:00:00.000000 +00:00', 6.6, 0.2), ('sitea', '2024-01-17 10:50:00.000000 +00:00', 6.4, 0.2), ('sitea', '2024-01-17 10:40:00.000000 +00:00', 6.2, 0.2), ('sitea', '2024-01-17 10:30:00.000000 +00:00', 6, 0), ('sitea', '2024-01-17 10:20:00.000000 +00:00', 6, 0), ('sitea', '2024-01-17 10:10:00.000000 +00:00', 6, 0), ('sitea', '2024-01-17 10:00:00.000000 +00:00', 6, 0), ('sitea', '2024-01-17 09:50:00.000000 +00:00', 6, 0), ('sitea', '2024-01-17 09:40:00.000000 +00:00', 6, 0), ('sitea', '2024-01-17 09:30:00.000000 +00:00', 6, 0), ('sitea', '2024-01-17 09:20:00.000000 +00:00', 6, 0), ('sitea', '2024-01-17 09:10:00.000000 +00:00', 6, 0), ('sitea', '2024-01-17 09:00:00.000000 +00:00', 6, 0), ('sitea', '2024-01-17 08:50:00.000000 +00:00', 6, 0), ('sitea', '2024-01-17 08:40:00.000000 +00:00', 6, 0), ('sitea', '2024-01-17 08:30:00.000000 +00:00', 6, 0.2), ('sitea', '2024-01-17 08:20:00.000000 +00:00', 5.8, 0), ('sitea', '2024-01-17 08:10:00.000000 +00:00', 5.8, 0), ('sitea', '2024-01-17 08:00:00.000000 +00:00', 5.8, 0.2), ('sitea', '2024-01-17 07:50:00.000000 +00:00', 5.6, 0), ('sitea', '2024-01-17 07:40:00.000000 +00:00', 5.6, 0), ('sitea', '2024-01-17 07:30:00.000000 +00:00', 5.6, 0), ('sitea', '2024-01-17 07:20:00.000000 +00:00', 5.6, 0), ('sitea', '2024-01-17 07:10:00.000000 +00:00', 5.6, 0), ('sitea', '2024-01-17 07:00:00.000000 +00:00', 5.6, 0), ('sitea', '2024-01-17 06:50:00.000000 +00:00', 5.6, 0), ('sitea', '2024-01-17 06:40:00.000000 +00:00', 5.6, 0), ('sitea', '2024-01-17 06:30:00.000000 +00:00', 5.6, 0), ('sitea', '2024-01-17 06:20:00.000000 +00:00', 5.6, 0), ('sitea', '2024-01-17 06:10:00.000000 +00:00', 5.6, 0), ('sitea', '2024-01-17 06:00:00.000000 +00:00', 5.6, 0), ('sitea', '2024-01-17 05:50:00.000000 +00:00', 5.6, 0), ('sitea', '2024-01-17 05:40:00.000000 +00:00', 5.6, 0), ('sitea', '2024-01-17 05:30:00.000000 +00:00', 5.6, 0), ('sitea', '2024-01-17 05:20:00.000000 +00:00', 5.6, 0), ('sitea', '2024-01-17 05:10:00.000000 +00:00', 5.6, 0), ('sitea', '2024-01-17 05:00:00.000000 +00:00', 5.6, 0), ('sitea', '2024-01-17 04:50:00.000000 +00:00', 5.6, 0), ('sitea', '2024-01-17 04:40:00.000000 +00:00', 5.6, 0), ('sitea', '2024-01-17 04:30:00.000000 +00:00', 5.6, 0), ('sitea', '2024-01-17 04:20:00.000000 +00:00', 5.6, 0), ('sitea', '2024-01-17 04:10:00.000000 +00:00', 5.6, 0), ('sitea', '2024-01-17 04:00:00.000000 +00:00', 5.6, 0), ('sitea', '2024-01-17 03:50:00.000000 +00:00', 5.6, 0), ('sitea', '2024-01-17 03:40:00.000000 +00:00', 5.6, 0), ('sitea', '2024-01-17 03:30:00.000000 +00:00', 5.6, 0), ('sitea', '2024-01-17 03:20:00.000000 +00:00', 5.6, 0), ('sitea', '2024-01-17 03:10:00.000000 +00:00', 5.6, 0), ('sitea', '2024-01-17 03:00:00.000000 +00:00', 5.6, 0), ('sitea', '2024-01-17 02:50:00.000000 +00:00', 5.6, 0), ('sitea', '2024-01-17 02:40:00.000000 +00:00', 5.6, 0), ('sitea', '2024-01-17 02:30:00.000000 +00:00', 5.6, 0), ('sitea', '2024-01-17 02:20:00.000000 +00:00', 5.6, 0), ('sitea', '2024-01-17 02:10:00.000000 +00:00', 5.6, 0), ('sitea', '2024-01-17 02:00:00.000000 +00:00', 5.6, 0), ('sitea', '2024-01-17 01:50:00.000000 +00:00', 5.6, 0), ('sitea', '2024-01-17 01:40:00.000000 +00:00', 5.6, 0), ('sitea', '2024-01-17 01:30:00.000000 +00:00', 5.6, 0), ('sitea', '2024-01-17 01:20:00.000000 +00:00', 5.6, 0), ('sitea', '2024-01-17 01:10:00.000000 +00:00', 5.6, 0.2), ('sitea', '2024-01-17 01:00:00.000000 +00:00', 5.4, 0.2), ('sitea', '2024-01-17 00:50:00.000000 +00:00', 5.2, 0.2), ('sitea', '2024-01-17 00:40:00.000000 +00:00', 5, 0.2), ('sitea', '2024-01-17 00:30:00.000000 +00:00', 4.8, 0.2), ('sitea', '2024-01-17 00:20:00.000000 +00:00', 4.6, 0.2), ('sitea', '2024-01-17 00:10:00.000000 +00:00', 4.4, 0.2), ('sitea', '2024-01-17 00:00:00.000000 +00:00', 4.2, 0.6), ('sitea', '2024-01-16 23:50:00.000000 +00:00', 3.6, 0.2), ('sitea', '2024-01-16 23:40:00.000000 +00:00', 3.4, 0), ('sitea', '2024-01-16 23:30:00.000000 +00:00', 3.4, 0.6), ('sitea', '2024-01-16 23:20:00.000000 +00:00', 2.8, 0.4), ('sitea', '2024-01-16 23:10:00.000000 +00:00', 2.4, 0), ('sitea', '2024-01-16 23:00:00.000000 +00:00', 2.4, 0.2), ('sitea', '2024-01-16 22:50:00.000000 +00:00', 2.2, 0.2), ('sitea', '2024-01-16 22:40:00.000000 +00:00', 2, 0.6), ('sitea', '2024-01-16 22:30:00.000000 +00:00', 1.4, 0.4), ('sitea', '2024-01-16 22:20:00.000000 +00:00', 1, 0.8), ('sitea', '2024-01-16 22:10:00.000000 +00:00', 0.2, 0.2), ('sitea', '2024-01-16 22:00:00.000000 +00:00', 16.4, 0.2), ('sitea', '2024-01-16 21:50:00.000000 +00:00', 16.2, 0.2), ('sitea', '2024-01-16 21:40:00.000000 +00:00', 16, 0), ('sitea', '2024-01-16 21:30:00.000000 +00:00', 16, 0), ('sitea', '2024-01-16 21:20:00.000000 +00:00', 16, 0), ('sitea', '2024-01-16 21:10:00.000000 +00:00', 16, 0), ('sitea', '2024-01-16 21:00:00.000000 +00:00', 16, 0.2), ('sitea', '2024-01-16 20:50:00.000000 +00:00', 15.8, 0), ('sitea', '2024-01-16 20:40:00.000000 +00:00', 15.8, 0.2), ('sitea', '2024-01-16 20:30:00.000000 +00:00', 15.6, 0.6), ('sitea', '2024-01-16 20:20:00.000000 +00:00', 15, 1), ('sitea', '2024-01-16 20:10:00.000000 +00:00', 14, 2.6), ('sitea', '2024-01-16 20:00:00.000000 +00:00', 11.4, 0.6), ('sitea', '2024-01-16 19:50:00.000000 +00:00', 10.8, 0.2), ('sitea', '2024-01-16 19:40:00.000000 +00:00', 10.6, 0), ('sitea', '2024-01-16 19:30:00.000000 +00:00', 10.6, 0), ('sitea', '2024-01-16 19:20:00.000000 +00:00', 10.6, 0), ('sitea', '2024-01-16 19:10:00.000000 +00:00', 10.6, 0.2), ('sitea', '2024-01-16 19:00:00.000000 +00:00', 10.4, 0), ('sitea', '2024-01-16 18:50:00.000000 +00:00', 10.4, 0), ('sitea', '2024-01-16 18:40:00.000000 +00:00', 10.4, 0), ('sitea', '2024-01-16 18:30:00.000000 +00:00', 10.4, 0.2), ('sitea', '2024-01-16 18:20:00.000000 +00:00', 10.2, 0), ('sitea', '2024-01-16 18:10:00.000000 +00:00', 10.2, 0), ('sitea', '2024-01-16 18:00:00.000000 +00:00', 10.2, 0), ('sitea', '2024-01-16 17:50:00.000000 +00:00', 10.2, 0), ('sitea', '2024-01-16 17:40:00.000000 +00:00', 10.2, 0), ('sitea', '2024-01-16 17:30:00.000000 +00:00', 10.2, 0), ('sitea', '2024-01-16 17:20:00.000000 +00:00', 10.2, 0), ('sitea', '2024-01-16 17:10:00.000000 +00:00', 10.2, 0), ('sitea', '2024-01-16 17:00:00.000000 +00:00', 10.2, 0), ('sitea', '2024-01-16 16:50:00.000000 +00:00', 10.2, 0), ('sitea', '2024-01-16 16:40:00.000000 +00:00', 10.2, 0), ('sitea', '2024-01-16 16:30:00.000000 +00:00', 10.2, 0.4), ('sitea', '2024-01-16 16:20:00.000000 +00:00', 9.8, 1.4), ('sitea', '2024-01-16 16:10:00.000000 +00:00', 8.4, 1), ('sitea', '2024-01-16 16:00:00.000000 +00:00', 7.4, 0.8), ('sitea', '2024-01-16 15:50:00.000000 +00:00', 6.6, 1.6), ('sitea', '2024-01-16 15:40:00.000000 +00:00', 5, 0.8), ('sitea', '2024-01-16 15:30:00.000000 +00:00', 4.2, 1), ('sitea', '2024-01-16 15:20:00.000000 +00:00', 3.2, 2.6), ('sitea', '2024-01-16 15:10:00.000000 +00:00', 0.6, 0.6), ('sitea', '2024-01-16 15:00:00.000000 +00:00', 0, 0), ('sitea', '2024-01-16 14:50:00.000000 +00:00', 0, 0), ('sitea', '2024-01-16 14:40:00.000000 +00:00', 0, 0), ('sitea', '2024-01-16 14:30:00.000000 +00:00', 0, 0), ('sitea', '2024-01-16 14:20:00.000000 +00:00', 0, 0), ('sitea', '2024-01-16 14:10:00.000000 +00:00', 0, 0), ('sitea', '2024-01-16 14:00:00.000000 +00:00', 0, 0), ('sitea', '2024-01-16 13:50:00.000000 +00:00', 0, 0), ('sitea', '2024-01-16 13:40:00.000000 +00:00', 0, 0), ('sitea', '2024-01-16 13:30:00.000000 +00:00', 0, 0), ('sitea', '2024-01-16 13:20:00.000000 +00:00', 0, 0), ('sitea', '2024-01-16 13:10:00.000000 +00:00', 0, 0), ('sitea', '2024-01-16 13:00:00.000000 +00:00', 0, 0), ('sitea', '2024-01-16 12:50:00.000000 +00:00', 0, 0), ('sitea', '2024-01-16 12:40:00.000000 +00:00', 0, 0), ('sitea', '2024-01-16 12:30:00.000000 +00:00', 0, 0), ('sitea', '2024-01-16 12:20:00.000000 +00:00', 0, 0), ('sitea', '2024-01-16 12:10:00.000000 +00:00', 0, 0), ('sitea', '2024-01-16 12:00:00.000000 +00:00', 0, 0), ('sitea', '2024-01-16 11:50:00.000000 +00:00', 0, 0), ('sitea', '2024-01-16 11:40:00.000000 +00:00', 0, 0), ('sitea', '2024-01-16 11:30:00.000000 +00:00', 0, 0), ('sitea', '2024-01-16 11:20:00.000000 +00:00', 0, 0), ('sitea', '2024-01-16 11:10:00.000000 +00:00', 0, 0), ('sitea', '2024-01-16 11:00:00.000000 +00:00', 0, 0), ('sitea', '2024-01-16 10:50:00.000000 +00:00', 0, 0), ('sitea', '2024-01-16 10:40:00.000000 +00:00', 0, 0), ('sitea', '2024-01-16 10:30:00.000000 +00:00', 0, 0), ('sitea', '2024-01-16 10:20:00.000000 +00:00', 0, 0), ('sitea', '2024-01-16 10:10:00.000000 +00:00', 0, 0), ('sitea', '2024-01-16 10:00:00.000000 +00:00', 0, 0), ('sitea', '2024-01-16 09:50:00.000000 +00:00', 0, 0), ('sitea', '2024-01-16 09:40:00.000000 +00:00', 0, 0), ('sitea', '2024-01-16 09:30:00.000000 +00:00', 0, 0), ('sitea', '2024-01-16 09:20:00.000000 +00:00', 0, 0), ('sitea', '2024-01-16 09:10:00.000000 +00:00', 0, 0), ('sitea', '2024-01-16 09:00:00.000000 +00:00', 0, 0), ('sitea', '2024-01-16 08:50:00.000000 +00:00', 0, 0), ('sitea', '2024-01-16 08:40:00.000000 +00:00', 0, 0), ('sitea', '2024-01-16 08:30:00.000000 +00:00', 0, 0), ('sitea', '2024-01-16 08:20:00.000000 +00:00', 0, 0), ('sitea', '2024-01-16 08:10:00.000000 +00:00', 0, 0), ('sitea', '2024-01-16 08:00:00.000000 +00:00', 0, 0), ('sitea', '2024-01-16 07:50:00.000000 +00:00', 0, 0), ('sitea', '2024-01-16 07:40:00.000000 +00:00', 0, 0), ('sitea', '2024-01-16 07:30:00.000000 +00:00', 0, 0), ('sitea', '2024-01-16 07:20:00.000000 +00:00', 0, 0), ('sitea', '2024-01-16 07:10:00.000000 +00:00', 0, 0), ('sitea', '2024-01-16 07:00:00.000000 +00:00', 0, 0), ('sitea', '2024-01-16 06:50:00.000000 +00:00', 0, 0), ('sitea', '2024-01-16 06:40:00.000000 +00:00', 0, 0), ('sitea', '2024-01-16 06:30:00.000000 +00:00', 0, 0), ('sitea', '2024-01-16 06:20:00.000000 +00:00', 0, 0), ('sitea', '2024-01-16 06:10:00.000000 +00:00', 0, 0), ('sitea', '2024-01-16 06:00:00.000000 +00:00', 0, 0), ('sitea', '2024-01-16 05:50:00.000000 +00:00', 0, 0), ('sitea', '2024-01-16 05:40:00.000000 +00:00', 0, 0), ('sitea', '2024-01-16 05:30:00.000000 +00:00', 0, 0), ('sitea', '2024-01-16 05:20:00.000000 +00:00', 0, 0), ('sitea', '2024-01-16 05:10:00.000000 +00:00', 0, 0), ('sitea', '2024-01-16 05:00:00.000000 +00:00', 0, 0), ('sitea', '2024-01-16 04:50:00.000000 +00:00', 0, 0), ('sitea', '2024-01-16 04:40:00.000000 +00:00', 0, 0), ('sitea', '2024-01-16 04:30:00.000000 +00:00', 0, 0), ('sitea', '2024-01-16 04:20:00.000000 +00:00', 0, 0), ('sitea', '2024-01-16 04:10:00.000000 +00:00', 0, 0), ('sitea', '2024-01-16 04:00:00.000000 +00:00', 0, 0), ('sitea', '2024-01-16 03:50:00.000000 +00:00', 0, 0), ('sitea', '2024-01-16 03:40:00.000000 +00:00', 0, 0), ('sitea', '2024-01-16 03:30:00.000000 +00:00', 0, 0), ('sitea', '2024-01-16 03:20:00.000000 +00:00', 0, 0), ('sitea', '2024-01-16 03:10:00.000000 +00:00', 0, 0), ('sitea', '2024-01-16 03:00:00.000000 +00:00', 0, 0), ('sitea', '2024-01-16 02:50:00.000000 +00:00', 0, 0), ('sitea', '2024-01-16 02:40:00.000000 +00:00', 0, 0), ('sitea', '2024-01-16 02:30:00.000000 +00:00', 0, 0), ('sitea', '2024-01-16 02:20:00.000000 +00:00', 0, 0), ('sitea', '2024-01-16 02:10:00.000000 +00:00', 0, 0), ('sitea', '2024-01-16 02:00:00.000000 +00:00', 0, 0), ('sitea', '2024-01-16 01:50:00.000000 +00:00', 0, 0), ('sitea', '2024-01-16 01:40:00.000000 +00:00', 0, 0), ('sitea', '2024-01-16 01:30:00.000000 +00:00', 0, 0), ('sitea', '2024-01-16 01:20:00.000000 +00:00', 0, 0), ('sitea', '2024-01-16 01:10:00.000000 +00:00', 0, 0), ('sitea', '2024-01-16 01:00:00.000000 +00:00', 0, 0), ('sitea', '2024-01-16 00:50:00.000000 +00:00', 0, 0), ('sitea', '2024-01-16 00:40:00.000000 +00:00', 0, 0), ('sitea', '2024-01-16 00:30:00.000000 +00:00', 0, 0), ('sitea', '2024-01-16 00:20:00.000000 +00:00', 0, 0), ('sitea', '2024-01-16 00:10:00.000000 +00:00', 0, 0), ('sitea', '2024-01-16 00:00:00.000000 +00:00', 0, 0)
;

-- QUERY database
SELECT
    site_name,
    date_time_utc AT TIME ZONE 'Australia/Hobart'                                 AS date_time_local,
    precip_10min,
    precip_since_9am,
    day_9am_val,
    SUM(precip_10min) OVER (PARTITION BY site_name, day_9am_val ORDER BY date_time_utc) AS precip_since_9am_cal
FROM
    (
        SELECT *,
            CASE
                WHEN DATE_PART('hour', date_time_utc AT TIME ZONE 'Australia/Hobart') > 9 THEN (date_time_utc AT TIME ZONE 'Australia/Hobart')::date
                WHEN DATE_PART('hour', date_time_utc AT TIME ZONE 'Australia/Hobart') < 9
                    THEN ((date_time_utc AT TIME ZONE 'Australia/Hobart')::date - INTERVAL '1 day')::date
                WHEN DATE_PART('minutes', date_time_utc AT TIME ZONE 'Australia/Hobart') > 0 THEN (date_time_utc AT TIME ZONE 'Australia/Hobart')::date
                ELSE ((date_time_utc AT TIME ZONE 'Australia/Hobart')::date - INTERVAL '1 day')::date
            END AS day_9am_val
        FROM
            temp_export
    ) tbl

ORDER BY
    site_name, date_time_utc DESC

LIMIT 10;

site_name	date_time_local	precip_10min	precip_since_9am	day_9am_val	precip_since_9am_cal
sitea	2024-01-18 17:00:00	0.2	10.8	2024-01-18	10.8
sitea	2024-01-18 16:50:00	0.4	10.6	2024-01-18	10.6
sitea	2024-01-18 16:40:00	0.2	10.2	2024-01-18	10.2
sitea	2024-01-18 16:30:00	0.2	10	2024-01-18	10.0
sitea	2024-01-18 16:20:00	0.4	9.8	2024-01-18	9.8
sitea	2024-01-18 16:10:00	0.2	9.4	2024-01-18	9.4
sitea	2024-01-18 16:00:00	0.2	9.2	2024-01-18	9.2
sitea	2024-01-18 15:50:00	0.2	9	2024-01-18	9.0
sitea	2024-01-18 15:40:00	0.2	8.8	2024-01-18	8.8
sitea	2024-01-18 15:30:00	0.4	8.6	2024-01-18	8.6

CREATE TABLE
INSERT 0 325