Horton

Thread

  1. Need to omit time during weekends from age calculations

    David Gauthier <davegauthierpg@gmail.com> — 2021-06-07T19:12:20Z

    Hi:

I suspect I'm not the first to ask about this but couldn't find anything
after googling for a bit.  So here goes....

I'd like to get the "age" difference between two times which span either
all or part of a weekend but exclude any time that transpired during the
weekend.
Example (please pardon the non-timestamp vals here...)

age('Monday-Noon','Prev-Friday-Noon')
would give me '1 day'.

...and...

age('Sunday-Noon','Prev-Friday-Noon')
would give me '12 hours'

You get the picture.

Has this wheel already been invented ?
I don't see an easy way to do this off-hand.
All Ears :-)

Thanks in Advance.

  2. Re: Need to omit time during weekends from age calculations

    Ron <ronljohnsonjr@gmail.com> — 2021-06-07T19:17:35Z

    On 6/7/21 2:12 PM, David Gauthier wrote:
> Hi:
>
> I suspect I'm not the first to ask about this but couldn't find anything 
> after googling for a bit.  So here goes....
>
> I'd like to get the "age" difference between two times which span either 
> all or part of a weekend but exclude any time that transpired during the 
> weekend.
> Example (please pardon the non-timestamp vals here...)
>
> age('Monday-Noon','Prev-Friday-Noon')
> would give me '1 day'.
>
> ...and...
>
> age('Sunday-Noon','Prev-Friday-Noon')
> would give me '12 hours'
>
> You get the picture.
>
> Has this wheel already been invented ?
> I don't see an easy way to do this off-hand.
> All Ears :-)
>
> Thanks in Advance.

You seem to be asking about counting work days.  Am I misunderstanding?

-- 
Angular momentum makes the world go 'round.

  3. Re: Need to omit time during weekends from age calculations

    Joe Conway <mail@joeconway.com> — 2021-06-07T19:34:56Z

    On 6/7/21 3:12 PM, David Gauthier wrote:
> Hi:
> 
> I suspect I'm not the first to ask about this but couldn't find anything 
> after googling for a bit.  So here goes....
> 
> I'd like to get the "age" difference between two times which span either 
> all or part of a weekend but exclude any time that transpired during the 
> weekend.
> Example (please pardon the non-timestamp vals here...)
> 
> age('Monday-Noon','Prev-Friday-Noon')
> would give me '1 day'.
> 
> ...and...
> 
> age('Sunday-Noon','Prev-Friday-Noon')
> would give me '12 hours'
> 
> You get the picture.
> 
> Has this wheel already been invented ?
> I don't see an easy way to do this off-hand.
> All Ears :-)

Perhaps not the prettiest of solutions, but what about something like this?

8<----------------------------------------
CREATE OR REPLACE FUNCTION nonweekendhours(startts timestamptz, endts 
timestamptz)
RETURNS interval AS $$
   SELECT
     (SUM(case when extract(dow from g.ts) > 0
                and extract(dow from g.ts) < 6 then
            1
          else
            0 end) || ' hours')::interval
   FROM generate_series(startts, endts - '1 hour'::interval,'1 hour') AS 
g(ts)
$$ LANGUAGE sql;

SELECT nonweekendhours('2021-06-04 12:00:00','2021-06-06 12:00:00');
  nonweekendhours
-----------------
  12:00:00
(1 row)

SELECT nonweekendhours('2021-06-04 12:00:00','2021-06-07 12:00:00');
  nonweekendhours
-----------------
  24:00:00
(1 row)

SELECT nonweekendhours('2021-06-04 12:00:00','2021-06-11 12:00:00');
  nonweekendhours
-----------------
  120:00:00
(1 row)
8<----------------------------------------

HTH,

Joe
-- 
Crunchy Data - http://crunchydata.com
PostgreSQL Support for Secure Enterprises
Consulting, Training, & Open Source Development

  4. Re: Need to omit time during weekends from age calculations

    Tom Lane <tgl@sss.pgh.pa.us> — 2021-06-07T19:39:15Z

    Joe Conway <mail@joeconway.com> writes:
> On 6/7/21 3:12 PM, David Gauthier wrote:
>> I'd like to get the "age" difference between two times which span either 
>> all or part of a weekend but exclude any time that transpired during the 
>> weekend.

I'm a bit suspicious of this problem statement.  I doubt there are many
practical applications where you wouldn't also wish to exclude holidays,
for somebody's definition of holidays.  Of course, that makes it a lot
messier since you need a source of data for that.

			regards, tom lane

  5. Re: Need to omit time during weekends from age calculations

    Pavel Stehule <pavel.stehule@gmail.com> — 2021-06-07T19:45:38Z

    po 7. 6. 2021 v 21:17 odesílatel Ron <ronljohnsonjr@gmail.com> napsal:

> On 6/7/21 2:12 PM, David Gauthier wrote:
>
> Hi:
>
> I suspect I'm not the first to ask about this but couldn't find anything
> after googling for a bit.  So here goes....
>
> I'd like to get the "age" difference between two times which span either
> all or part of a weekend but exclude any time that transpired during the
> weekend.
> Example (please pardon the non-timestamp vals here...)
>
> age('Monday-Noon','Prev-Friday-Noon')
> would give me '1 day'.
>
> ...and...
>
> age('Sunday-Noon','Prev-Friday-Noon')
> would give me '12 hours'
>
> You get the picture.
>
> Has this wheel already been invented ?
> I don't see an easy way to do this off-hand.
> All Ears :-)
>
> Thanks in Advance.
>
>
> You seem to be asking about counting work days.  Am I misunderstanding?
>

orafce has functions for business calendar  with holidays

https://github.com/orafce/orafce

plvdate.add_bizdays(day date, days int) date

Regards

Pavel


> --
> Angular momentum makes the world go 'round.
>

  6. Re: Need to omit time during weekends from age calculations

    David Gauthier <davegauthierpg@gmail.com> — 2021-06-07T21:01:23Z

    >>You seem to be asking about counting work days.
Ya, counting work days, or discounting non-work days.  Two sides, same coin.

Thanks Joe.  I think the nonweekendhours solution should be good enough for
what I need.

Yes, holidays too would be the best.  But for practical purposes, excluding
Sat&Sun is good enough for this particular problem.

Thanks Everyone !


On Mon, Jun 7, 2021 at 3:46 PM Pavel Stehule <pavel.stehule@gmail.com>
wrote:

>
>
> po 7. 6. 2021 v 21:17 odesílatel Ron <ronljohnsonjr@gmail.com> napsal:
>
>> On 6/7/21 2:12 PM, David Gauthier wrote:
>>
>> Hi:
>>
>> I suspect I'm not the first to ask about this but couldn't find anything
>> after googling for a bit.  So here goes....
>>
>> I'd like to get the "age" difference between two times which span either
>> all or part of a weekend but exclude any time that transpired during the
>> weekend.
>> Example (please pardon the non-timestamp vals here...)
>>
>> age('Monday-Noon','Prev-Friday-Noon')
>> would give me '1 day'.
>>
>> ...and...
>>
>> age('Sunday-Noon','Prev-Friday-Noon')
>> would give me '12 hours'
>>
>> You get the picture.
>>
>> Has this wheel already been invented ?
>> I don't see an easy way to do this off-hand.
>> All Ears :-)
>>
>> Thanks in Advance.
>>
>>
>> You seem to be asking about counting work days.  Am I misunderstanding?
>>
>
> orafce has functions for business calendar  with holidays
>
> https://github.com/orafce/orafce
>
> plvdate.add_bizdays(day date, days int) date
>
> Regards
>
> Pavel
>
>
>> --
>> Angular momentum makes the world go 'round.
>>
>

  7. Re: Need to omit time during weekends from age calculations

    Samuel Gendler <sgendler@ideasculptor.com> — 2021-06-08T01:11:59Z

    On Mon, Jun 7, 2021 at 2:01 PM David Gauthier <davegauthierpg@gmail.com>
wrote:

> Thanks Joe.  I think the nonweekendhours solution should be good enough
> for what I need.
>
> Yes, holidays too would be the best.  But for practical purposes,
> excluding Sat&Sun is good enough for this particular problem.
>

I've solved this in the past with a time dimension table that includes
columns labeling weekends and holidays.  Then I can query for the count of
intervals (for whatever interval size my time dimension uses) in the date
range joined to time_dimension where weekday is true and holiday is false,
and multiply the count by the number of hours in an interval.