Choose a fixed number of rows from two tables in sql

Question

I have two tables

table1 (p double precesion, t timestamp without zone) 

and

table2 (v double precision, t timestamp without zone) 

with values ​​for every minute.

How to choose 60 lines of the form (t, v, p) for every minute at a given hour?

Some values ​​may be missing, in which case the corresponding return value must be NULL.

Each hour should have exactly 60 rows.

I am using PostgreSQL.

Answer 1

SELECT  '2010-01-01 01:00:00'::DATE + (m || ' minute')::INTERVAL, v, p
FROM    generate_series(0, 59) m
LEFT JOIN
        table1 t1
ON      t1.t >= '2010-01-01 01:00:00'::DATE + (m || ' minute')::INTERVAL
        AND t1.t < '2010-01-01 01:00:00'::DATE + (m + 1 || ' minute')::INTERVAL
LEFT JOIN
        table2 t2
ON      t2.t >= '2010-01-01 01:00:00'::DATE + (m || ' minute')::INTERVAL
        AND t2.t < '2010-01-01 01:00:00'::DATE + (m + 1 || ' minute')::INTERVAL

This suggests that the entries are unique every minute (although seconds may vary).

If this is not the case, you will receive duplicates.

In this case, you can copy them (for example, the amount):

SELECT  '2010-01-01 01:00:00'::DATE + (m || ' minute')::INTERVAL,
        COALESCE
        (
        SELECT  SUM(p)
        FROM    table1 t1
        WHERE   t1.t >= '2010-01-01 01:00:00'::DATE + (m || ' minute')::INTERVAL
                AND t1.t < '2010-01-01 01:00:00'::DATE + (m + 1 || ' minute')::INTERVAL
        ), 0) p,
        COALESCE
        (
        SELECT  SUM(v)
        FROM    table2 t2
        WHERE   t2.t >= '2010-01-01 01:00:00'::DATE + (m || ' minute')::INTERVAL
                AND t2.t < '2010-01-01 01:00:00'::DATE + (m + 1 || ' minute')::INTERVAL
        ), 0) v
FROM    generate_series(0, 59) m