Pivot time series into date period columns (sum aggregation)

Unfortunately I am not a database expert and I am trying to rebuild a PostgreSQL table as follows.

I have many many rows with charge points (which have a corresponding ID). Beside this I have a date incl. time and a charging duration (always 15 minutes). I would like to sum these load times so that I have all the IDs in the rows and all the days of February (2/1/23 to 2/28/23) in the columns. To fill are the sums of the loading times per day.

Example:

I would like to have it like this:

Could you please help?

Thank you & Regards, Fabi

You can achieve this with crosstab from the tablefunc module. Something like:

SELECT * FROM crosstab(
	'SELECT evse_id, created_at::date::text, SUM(duration) as dayduration 
FROM demo
GROUP BY evse_id, created_at::date ORDER BY 1, 2',
	'SELECT date_trunc(''day'', dd)::date::text FROM generate_series(''2023-02-01'',''2023-02-28'', ''1 day''::interval) dd'
) AS report(evse_id text, "2023-02-01" int, "2023-02-02" int, "2023-02-03" int, "2023-02-04" int, 
			"2023-02-05" int, "2023-02-06" int, "2023-02-07" int, "2023-02-08" int, 
			"2023-02-09" int, "2023-02-10" int, "2023-02-11" int, "2023-02-12" int, 
			"2023-02-13" int, "2023-02-14" int, "2023-02-15" int, "2023-02-16" int, 
			"2023-02-17" int, "2023-02-18" int, "2023-02-19" int, "2023-02-20" int, 
			"2023-02-21" int, "2023-02-22" int, "2023-02-23" int, "2023-02-24" int, 
			"2023-02-25" int, "2023-02-26" int, "2023-02-27" int, "2023-02-28" int);

By way of explanation, crosstab here takes two parameters, the first (source_sql) defines the source data. This must have precisely three columns, of which first is the row "names", the second the columns and the third is (usually) the aggregated value.

The second parameter (category_sql) gives us the columns, which in this case we generate using a series.

Finally we need to define the output columns, which are the row names plus the data columns. Note the number of columns defined must match the number of columns in the exported data.

If you don't already have the tablefunc module, running the command create extension tablefunc; should create it for you.

Be sure to replace demo with your table name!