calculate date in 1 min interval postgres-CodePudding

I have a Table like this:

create table simulation_book_data 
(
    dt date,
    t time without time zone,
    price numeric   
)

the lines look like this:

"2021-11-17"    "06:39:37.7663" 2
"2021-11-17"    "06:39:38.7663" 5
"2021-11-17"    "06:39:39.7663" 0
"2021-11-17"    "06:39:40.7663" 9
"2021-11-17"    "06:39:41.7663" 3
"2021-11-17"    "06:39:42.7663" 7
"2021-11-17"    "06:39:43.7663" 5
"2021-11-17"    "06:39:44.7663" 6
"2021-11-17"    "06:39:45.7663" 3
"2021-11-17"    "06:39:46.7663" 1
"2021-11-17"    "06:39:47.7663" 2
"2021-11-17"    "06:39:48.7663" 5
"2021-11-17"    "06:39:49.7663" 8
"2021-11-17"    "06:39:50.7663" 9
"2021-11-17"    "06:39:51.7663" 4
"2021-11-17"    "06:39:52.7663" 5
"2021-11-17"    "06:39:53.7663" 6
"2021-11-17"    "06:39:54.7663" 6
"2021-11-17"    "06:39:55.7663" 7
"2021-11-17"    "06:39:56.7663" 8
"2021-11-17"    "06:39:57.7663" 9
"2021-11-17"    "06:39:58.7663" 7
"2021-11-17"    "06:39:59.7663" 8
"2021-11-17"    "06:40:00.7663" 9
"2021-11-17"    "06:40:01.7663" 1

I want to sum the price column per minute, but couldn't find how to do range with time (not timestamp)

CodePudding user response：

Call the function sum() with a group by using date_trunc:

SELECT date_trunc('minute',dt t),sum(price) 
FROM simulation_book_data
GROUP BY 1;

     date_trunc      | sum 
--------------------- -----
 2021-11-17 06:39:00 | 125
 2021-11-17 06:40:00 |  10
(2 rows)

Demo: db<>fiddle

CodePudding user response：

you can use sum() as a window function, to_char converts a timestamp to a format 'HH24:MI' omitting seconds

select distinct on (dt, to_char(sbd.t,  'HH24:MI'::text))
sbd.dt,
sbd.t,
to_char(sbd.t,  'HH24:MI'::text),
sum(price) over (partition by dt, to_char(sbd.t,  'HH24:MI'::text))
from simulation_book_data sbd

result:

2021-11-17  06:39:37    06:39   125
2021-11-17  06:40:00    06:40   10