CODEWITHSUNDEEP
sqlpostgresql
MikeC8
MikeC8

Reputation: 3923

Summing time ranges between time ranges in PostgreSQL

Let's say I have a table that looks something like this:

id          start              end
-----+-----------------------+---------------------
  1  |   Jan 15 2:30 pm      |     Jan 15 2:45 pm
  2  |   Jan 15 3:16 pm      |     Jan 16 5:14 am
  :  |         :             |           :

What I need to do is get the total sum of the duration of time that is between a certain time range.

For example, let's say I needed the sum of all time that was between query_start = Jan 15 at 1pm and query_end = Jan 15 at 3pm. That would be simple, because I could look for time where start >= query_start and end <= query_end and then sum up the differences between start and end.

However, it gets a little more complicated when the query range does not completely encompass the range in the row.

For example, if I was querying for all time between Jan 15 2:15 pm and Jan 15 2:35 pm, then the correct result would be 5 minutes, because 5 minutes of row 1 was between that time.

Also, for instance, if my query range was between Jan 15 2:40 and Jan 15 2:41 then I would want to get a sum total of 1 minute.

All I really need here is the sum, not the individual rows.

Does PostgreSQL support this natively?

If not, does anyone have any ideas about how I could write a function to do it?

Upvotes: 0

Views: 920

Answers (2)

NA.
NA.

Reputation: 6589

You can use ranges to solve this.

First find the ranges you are interested in with the && operator like

SELECT * FROM foo WHERE tsrange(start, stop) && tsrange('(2016-01-07 10:30', '2016-01-07 16:30)')

Then use the intersect * operator to cut away the time outside the range/interval you are interested in.

SELECT tsrange('(2016-01-07 10:30', '2016-01-07 16:30)') * tsrange('(2016-01-07 13:00', '2016-01-07 13:30)')

What's left is to sum it up. A complete query would be something like

SELECT 
  SUM(
    upper(tsrange(start, stop) * tsrange('(2016-01-07 10:30', '2016-01-07 16:30)')) 
    - 
    lower(tsrange(start, stop) * tsrange('(2016-01-07 10:30', '2016-01-07 16:30)')))
  FROM foo WHERE tsrange(start, stop) && tsrange('(2016-01-07 10:30', '2016-01-07 16:30)');

Adjust your ranges to be open or closed depending on how you want the result.

Upvotes: 0

Burleigh Bear
Burleigh Bear

Reputation: 3314

I haven't got time to test this, but I think that you can make a query where rather than do start > query_start and end < query_end you can do 

query_start < end and query_end > start

as the where clause, which will give you any periods that overlap. Then rather than summing the end - start for each period, you can remove the overlap with

sum(min(end, query_end) - max(query_start, start))

I haven't tested this.

Upvotes: 1

Related Questions