I have a table with a starting time, finishing time and delivery columns. The starting and finishing times are of the timestamp without time zone data type (YYYY-MM-DD HH:MM:SS). I would like to write a query script, which generates a table with three columns; date, delivery and average time taken.

In other words, every row contains the average time taken for a delivery on a given day.

How could I achieve this ?

CodePudding user response：

We can use ::date to extract the date and then use this in group by.

select 
  *,
  finishing - starting time_difference
from t

starting            | finishing           | delivery | time_difference
:------------------ | :------------------ | -------: | :--------------
2022-05-07 08:00:00 | 2022-05-07 10:00:00 |        1 | 02:00:00       
2022-05-07 12:00:00 | 2022-05-07 16:00:00 |        2 | 04:00:00       

select 
  starting::date "date",
  AVG(finishing - starting) average_time
from t
group by starting::date

date       | average_time
:--------- | :-----------
2022-05-07 | 03:00:00    

db<>fiddle here