Join rows with fuzzy time data to regular time slots

Question

I have PostgreSQL table that look like this:

CREATE TABLE tb1 (
id integer,
name text,
date date,
time time without tz
);

With following query I am creating table with row_id's and time starting from 09:00 with increment every 10 minutes

(
SELECT g AS id, '09:00'::time + '10 min'::interval * (g-1) AS time
FROM   generate_series (1,10) g
) g
left JOIN tb1 AS t1 USING (time)

After join I have new table which contain row's from table tb1 but only if time in tb1 have same time as one generated by '09:00'::time + '10 min'::interval * (g-1) AS time.

How to achieve that all rows with time from tb1 is "inserted" into generated table. If time generated is same than time from tb1 then no duplication should happen.

Answer 1

If you can guarantee there is no more than one row for every 10-min-slot in tb1, this should be fastest:

SELECT *
FROM  (
   SELECT g AS id, '09:00'::time + '10 min'::interval * (g-1) AS time_slot
   FROM   generate_series (1,10) g
   ) g
LEFT  JOIN tb1 t1 ON t1.time >= g.time_slot
                 AND t1.time <  g.time_slot + '10 min'::interval

The join condition is sargable, so an index on time can be used.

If there can be multiple rows per 10-min-slot in the source and you need a single row in the result, you need to aggregate one way or the other. Like:

SELECT g.id, g.time_slot, t1.time, min(t1.whatever) AS min_whatever
FROM  (
   SELECT g AS id, '09:00'::time + '10 min'::interval * (g-1) AS time_slot
   FROM   generate_series (1,10) g
   ) g
LEFT  JOIN tb1 t1 ON t1.time >= g.time_slot
                 AND t1.time <  g.time_slot + '10 min'::interval
GROUP BY 1, 2, 3;

Answer 2

I auspect you want a full outer join.

(
SELECT g AS id, '09:00'::time + '10 min'::interval * (g-1) AS time
FROM   generate_series (1,10) g
) g
FULL OUTER JOIN tb1 AS t1 USING (time)