PostgreSQL › PostgreSQL - general

Need to omit time during weekends from age calculations

_‹ Previous Topic Next Topic _›

Classic

List

Threaded

7 messages Options

David Gauthier

Need to omit time during weekends from age calculations

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.

Ron-2

Re: Need to omit time during weekends from age calculations

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.

Joe Conway

Re: Need to omit time during weekends from age calculations

In reply to this post by David Gauthier

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

Tom Lane-2

Re: Need to omit time during weekends from age calculations

Joe Conway < > 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

Pavel Stehule

Re: Need to omit time during weekends from age calculations

In reply to this post by Ron-2

po 7. 6. 2021 v 21:17 odesílatel Ron < > 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.

David Gauthier

Re: Need to omit time during weekends from age calculations

>> 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 < > wrote:

po 7. 6. 2021 v 21:17 odesílatel Ron < > 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.

Samuel Gendler

Re: Need to omit time during weekends from age calculations

On Mon, Jun 7, 2021 at 2:01 PM David Gauthier < > 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.