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

Time:10-22

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

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

  • Related