Обсуждение: 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.
On 6/7/21 2:12 PM, David Gauthier wrote:
You seem to be asking about counting work days. Am I misunderstanding?
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.
Angular momentum makes the world go 'round.
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
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
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
plvdate.add_bizdays(day date, days int) date
Regards
Pavel
--
Angular momentum makes the world go 'round.
>>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 holidaysplvdate.add_bizdays(day date, days int) dateRegardsPavel--
Angular momentum makes the world go 'round.
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.