Is there a way to do a selective sum using a time interval in Postgres?-CodePudding

I have two tables, the first table has columns: id, start_time, and end_time. The second table has columns: id, timestamp, value. Is there a way to make a sum of table 2 based on the conditions in table 1?

Table 1:

id	start_date	end_date
5	2000-01-01 01:00:00	2000-01-05 02:45:00
5	2000-01-10 01:00:00	2000-01-15 02:45:00
6	2000-01-01 01:00:00	2000-01-05 02:45:00
6	2000-01-11 01:00:00	2000-01-12 02:45:00
6	2000-01-15 01:00:00	2000-01-20 02:45:00

Table 2:

id	timestamp	value
5	2000-01-01 05:00:00	1
5	2000-01-01 06:00:00	2
6	2000-01-01 05:00:00	1
6	2000-01-11 05:00:00	2
6	2000-01-15 05:00:00	2
6	2000-01-15 05:30:00	2

Desired result:

id	start_date	end_date	Sum
5	2000-01-01 01:00:00	2000-01-05 02:45:00	3
5	2000-01-10 01:00:00	2000-01-15 02:45:00	null
6	2000-01-01 01:00:00	2000-01-05 02:45:00	1
6	2000-01-11 01:00:00	2000-01-12 02:45:00	2
6	2000-01-15 01:00:00	2000-01-20 02:45:00	4

CodePudding user response：

Try this :

SELECT a.id, a.start_date, a.end_date, sum(b.value) AS sum
  FROM table1 AS a
  LEFT JOIN table2 AS b
    ON b.id = a.id
   AND b.timestamp >= a.start_date
   AND b.timestamp < a.end_date
 GROUP BY a.id, a.start_date, a.end_date