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

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 :

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 :

CodePudding user response：

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

SELECT *, SUM(duree_seconde) OVER (PARTITION BY connexion::date)
FROM histodureeconnexion;

Demo: db<>fiddle