How to sum Kilowatts in PostgreSQL, grouping them by time intervals?

Question

PostgreSQL

Good day to all. There are 2 tables in Postgresql. In one id, event_start(timestamp with time zone), event_end(timestamp with time zone), in the other event_time(timestamp with time zone), kwh(int). You need to create a select with columns id, event_start, event_end, SUM(kwh). How to find this amount if you need to sum up all the spent kilowatt-hours that were recorded in the time intervals between event_start and event_end?
I'm just learning, please don't throw slippers at me.

Table periods:
id event_start event_end
500 "2020-09-02 18:22:00+07" "2020-09-02 18:22:22+07"
499 "2020-09-02 18:19:00+07" "2020-09-02 18:19:25+07"
500 "2020-09-02 18:15:00+07" "2020-09-02 18:15:
499 "2020-09-02 18:14:00+07" "2020-09-02 18:14:05+07"
500 "2020-09-02 18:05:00+07" "2020-09-02 18:05:10+07"

Table energy:
event_time
kwh "2020-09-01 04:48:00+07" 1.57037
"2020-08-01 04:00:00+07" 1.44356
"2020-08-01 04 :01:00+07" 1.49
"2020-08-01 04:02:00+07" 1.52698
"2020-08-01 04:03:00+07" 1.56503
"2020-08-01 04:04:00+ 07" 1.71035
"2020-08-01 04:05:00+07" 1.70528
"2020-08-01 04:06:00+07" 1.51452
"2020-08-01 04:07:00+07" 1.57223
"2020 -08-01 04:08:00+07" 0.91082

Here's an example of what it looks like. Of course, there are much more lines

Answer 1

select p.endpoint_id,
       p.mode_start as event_start,
       (p.mode_start + p.mode_duration) as event_end,
       sum(e.kwh) as sum_kwh
from periods p
join energy e on e.endpoint_id = p.endpoint_id -- связываем выставленный период для прибора с показаниями прибора
             and p.mode_start <= e.event_time -- связывание начало периода с моментом замера 
             and (p.mode_start + p.mode_duration) > e.event_time -- связывание конца периода с моментом замера
group by p.endpoint_id, p.mode_start, (p.mode_start + p.mode_duration)
order by p.endpoint_id, p.mode_start

Note.
1. The request was made without debugging, debugging is required.
2. If the periods overlap for the same device, then the measurement, in the interval where the overlap occurs, will be included in the sum in both periods.
3. If a period is not specified for events, they will not be taken into account. You can write such a bunch through left join like this: "... energy e left join periods p on ... ", then the amount of unaccounted energy will fall into a separate line, in which there will be no period.
Request to display unaccounted measurements:

select e.endpoint_id,
       p.mode_start as event_start,
       (p.mode_start + p.mode_duration) as event_end,
       sum(e.kwh) as sum_kwh
from  energy e 
left join periods p on e.endpoint_id = p.endpoint_id
             and p.mode_start <= e.event_time
             and (p.mode_start + p.mode_duration) > e.event_time
group by e.endpoint_id, p.mode_start, (p.mode_start + p.mode_duration)
order by e.endpoint_id,
         p.mode_start nulls first -- ставим первой строкой сумму замеров с неучтенными значениями (в пределах одного прибора)