How to calculate 'x' working hours from now? (sql or postgresql 9.3 preferred)

Question

Firstly the answer to this could be generic or plain sql, however I'm developing it in postgresql 9.3.x and will present the setup in this format as it has some handy tsrange data types.

I have a requirement to retrieve a timestamp given:

• a starting timestamp
• duration (interval)
• a set of timestamp ranges.

A working scenario for this would be calculating 24h from now (business hours only), today being Friday at 1300, the result would be next Thursday at 1300 (6 active working hours per day).

A very detailed answer provided by Erwin Brandstetter to a similar question can be found here.

To make replying easier I'll create a scenario:

Let's create a table with timestamp ranges in postgresql and populate them with some data

CREATE TABLE working_hours (id serial NOT NULL, trange tstzrange NOT NULL, CONSTRAINT whours_pkey PRIMARY KEY (id));

INSERT INTO working_hours (trange) VALUES 
    ('[2014-05-01 09:30:00+07, 2014-05-01 11:30:00+07]'),('[2014-05-01 13:00:00+07,2014-05-01 17:00:00+07]'),
    ('[2014-05-02 09:30:00+07, 2014-05-02 11:30:00+07]'),('[2014-05-02 13:00:00+07,2014-05-02 17:00:00+07]'),
    ('[2014-05-05 09:30:00+07, 2014-05-05 11:30:00+07]'),('[2014-05-05 13:00:00+07,2014-05-05 17:00:00+07]'),
    ('[2014-05-06 09:30:00+07, 2014-05-06 11:30:00+07]'),
    ('[2014-05-07 09:30:00+07, 2014-05-07 11:30:00+07]'),('[2014-05-07 13:00:00+07,2014-05-07 17:00:00+07]'),
    ('[2014-05-08 09:30:00+07, 2014-05-08 11:30:00+07]'),('[2014-05-08 13:00:00+07,2014-05-08 17:00:00+07]'),
    ('[2014-05-09 09:30:00+07, 2014-05-09 11:30:00+07]'),('[2014-05-09 13:00:00+07,2014-05-09 17:00:00+07]'),
    ('[2014-05-10 09:30:00+07, 2014-05-10 11:30:00+07]'),('[2014-05-10 13:00:00+07,2014-05-10 17:00:00+07]');

I feel this can be achieved through a SELECT statement rather than a function that has a loop in it.

My idea of this statement would be 2 or 3 parts:

• Firstly minus the remainder of the duration from 'trange', returning the id of the last trange to affect the duration with the remainder being >= 0.

• Secondly add the remainder as interval to either lower(trange) or upper(trange) dependent on the remainder falling in or out of the last trange.

Here are the reference pages for Postgresql Range Types and Range Functions & Operators.

I appreciate any feedback on this, even if it is a completely different way to tackle the challenge. Though I posted this as Postgresql 9.3, the answer could be any language or language agnostic.

Answer 1

SQL Fiddle

select
    upper(trange) -
    interval '1 second' *
    abs(extract(epoch from '1 hours' - accumulated))
    as target_time
from (
    select
        trange,
        sum(upper(trange) - lower(trange)) over(order by trange) as accumulated
    from (
        select trange * tstzrange('2014-05-02 09:45:15+07', 'infinity', '[]') as trange
        from working_hours
    ) wh
) s
where trange != 'empty'
order by trange
limit 1

Answer 2

After endeavoring on what I thought was a daunting task, the solution proved to be quite trivial. I used a select on a window function.

If someone can give a better solution to this, then please do.

I'll use the task time of 24 business hours in the future

EDIT: (Not Working Correctly!)

SELECT lower(trange) + (dur_total - (interval '24h')) 
FROM (
    SELECT trange, dur_total 
    FROM (
        SELECT trange, sum(upper(trange) - lower(trange)) 
          OVER (ORDER BY trange ASC) AS dur_total 
          FROM working_hours
          WHERE upper(trange) >= now()
     ) t
WHERE dur_total >= interval '24H' ORDER BY dur_total ASC LIMIT 1
) u;

EDIT This is working, using a test time of '2014-05-02 09:45:15+07' However it's quite a long solution.

Clodoaldo Neto has given an alternative solution which seems neater and is also very close to working.

I've included the sqlfiddle

SELECT lower(trange) 
+ ((interval '1 hours') - (dur_total - duration)) 
 + GREATEST ('0h','2014-05-02 09:45:15+07' 
             - (SELECT lower(trange)
                FROM working_hours WHERE upper(trange) >= '2014-05-02 09:45:15+07'
                ORDER BY lower(trange) ASC LIMIT 1))
AS event_time
FROM (
SELECT trange, duration, dur_total 
  FROM (
    SELECT trange, upper(trange) - lower(trange) AS duration, sum(upper(trange) - lower(trange)) 
    OVER (ORDER BY trange ASC) AS dur_total 
    FROM working_hours 
    WHERE upper(trange) >= '2014-05-02 09:45:15+07'
) t
WHERE dur_total >= interval '1 hours'
  + GREATEST ('0h','2014-05-02 09:45:15+07' 
             - (SELECT lower(trange)
                FROM working_hours WHERE upper(trange) >= '2014-05-02 09:45:15+07'
                ORDER BY lower(trange) ASC LIMIT 1))
  ORDER BY dur_total ASC LIMIT 1
) u;

Answer 3

I came across this question just now and I find it really neat. The answer of Clodoaldo is a very nice one and I've based my query on it. Here it goes:

WITH t(s,i) AS (
    VALUES ('2014-05-02 09:45:15+07'::timestamptz, '1h45m'::interval)
)
SELECT trange,accum, remain,
       CASE WHEN accum >= t.i
             AND coalesce(lag(accum) OVER (ORDER BY trange), '0') < t.i
       THEN upper(trange) + remain END                              AS target,
       t.i
  FROM (
    SELECT trange,
           sum(upper(trange) - lower(trange)) OVER (ORDER BY trange) AS accum,
           i - sum(upper(trange) - lower(trange))
             OVER (ORDER BY trange)                                  AS remain
      FROM (
        SELECT trange * tstzrange(t.s, 'infinity', '[]') trange, t.i
          FROM working_hours CROSS JOIN t
      ) wh
  ) s
  CROSS JOIN t;

I return more rows then necessary for better visibility. You can wrap this query into a sub-select and filter for target IS NOT NULL.