CODEWITHSUNDEEP
sqldatabasepostgresqldategroup-by
Puti
Puti

Reputation: 101

Postgres: Grouping an average from timestamps

I am having some issues trying to create an PostgreSQL query that does the following:

The data looks something like this:

|colour | start | stop |
|blue|2020-02-03 22:18:08.561+00|2020-02-03 22:18:09.674+00|
|blue|2020-02-03 22:18:37.902+00|2020-02-03 22:18:40.479+00|
|red|2020-02-03 22:18:41.121+00|2020-02-03 22:18:42.272+00|
|green|2020-02-03 22:18:41.113+00|2020-02-03 22:18:42.835+00|
|red|2020-02-03 22:18:41.105+00|2020-02-03 22:18:43.223+00|

So far, I've only got the duration in seconds using:

SELECT colour, start, stop, 
EXTRACT(EPOCH FROM (stop - start)) as durationSeconds
FROM public.colour_record

That's as far as I've gotten. Any help wouldn't be great appreciated.

Upvotes: 0

Views: 168

Answers (1)

GMB
GMB

Reputation: 222402

I you want the average duration per color, you can use aggregation as follows:

select 
    colour, 
    avg(extract(epoch from (stop - start))) as avg_duration_seconds
from public.colour_record
group by colour

Upvotes: 1

Related Questions