PostgreSQL time range duration over time series with default end if null

Question

I need to be able to calculate the duration (in seconds) between two time stamps as an aggregate over a time series using a default end_datetime if it is null.

Imagine you have something like a punch card when you puch in and out: username, start_datetime, end_datetime

What I want is a generated time series of the last N minutes with the duration for all users that overlap within that time frame. So it would be the SUM(end_datetime - start_datetime) where you would COALESCE a default end_datetime if it is null.

So the basic pieces I think I need are:

Generate the time interval:

select TIMESTAMP '2013-01-01 12:01:00' - (interval '1' minute * generate_series(0,5)) as timestamps;

COALESCE a default end_datetime

COALESCE(end_datetime, NOW())

Figure out the seconds difference between the start and end dates

So if one user logged in at 11:56:50 and it is now 12:01:40 we should get a table like:

     timestamps          duration
-------------------------------------
 2013-01-01 12:01:00        40
 2013-01-01 12:00:00        60
 2013-01-01 11:59:00        60
 2013-01-01 11:58:00        60
 2013-01-01 11:57:00        60
 2013-01-01 11:56:00        10

Tomas Greif · Accepted Answer · 2013-09-18 06:47:16Z

2

with t as (select '2013-01-01 11:56:50'::timestamp startt, '2013-01-01 12:01:40'::timestamp endt)

select
   timestamps,
   extract(epoch from 
   case 
      when timestamps=date_trunc('minute',startt) then date_trunc('minute',startt) + interval '1 minute' - startt
      when timestamps =date_trunc('minute',endt) then endt- date_trunc('minute',endt)
      else interval '60 seconds' end) as durations
from
   (select generate_series(date_trunc('minute',startt),date_trunc('minute',endt),'1 minute') timestamps, * from t) a
order by
   timestamps desc;

2013-01-01 12:01:00;40
2013-01-01 12:00:00;60
2013-01-01 11:59:00;60
2013-01-01 11:58:00;60
2013-01-01 11:57:00;60
2013-01-01 11:56:00;10

If you have multiple rows with start and end timestamp than the following will work:

select
   id,
   timestamps,
   extract(epoch from 
   case 
      when timestamps=date_trunc('minute',startt) then date_trunc('minute',startt) + interval '1 minute' - startt
      when timestamps =date_trunc('minute',endt) then endt- date_trunc('minute',endt)
      else interval '60 seconds' end) as durations
from
   (
      select 
      id,
      generate_series(date_trunc('minute',startt) ,
                      coalesce(date_trunc('minute',endt),date_trunc('minute',Now())),'1 minute') as timestamps,
      startt, endt
   from test
   ) a
order by
   id, timestamps desc

SQLFiddle

edited Sep 18, 2013 at 6:47

answered Sep 18, 2013 at 6:37

Tomas Greif

22.9k25 gold badges114 silver badges162 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

hazmat Over a year ago

Thanks! I have been pulling my hair out on SQLFiddle trying to figure this out. I had the some of the parts but couldn't imagine the whole thing put together.

Collectives™ on Stack Overflow

PostgreSQL time range duration over time series with default end if null

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related