How to group rows by the date part of a timestamp column?

user_1045704 0
Viewed 63

There is a table having timestamp column :

create table histodureeconnexion ( id serial primary key, iduser int, connexion timestamp, deconnexion timestamp, duree_seconde integer );

I want to get all rows and the sum of the duree_seconde column based on the date part of the connexion column ; that means I want to sum all the duree_seconde column values for each same day, for example if there are data like this :

enter image description here

how can I get the sum of each duree_seconde column grouped by the date part of the connexion column ? That is : I want to get something like this :

enter image description here

postgresql sql
1 Answers

Try with a window function and in the PARTITION BY clause cast the timestamp to date:

SELECT *, SUM(duree_seconde) OVER w
FROM histodureeconnexion
WINDOW w AS (PARTITION BY connexion::date ORDER BY connexion
             RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW);

Demo: db<>fiddle

user_22753880
Related