# Need to omit time during weekends from age calculations

7 messages
Open this post in threaded view
|

## 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.
Open this post in threaded view
|

## 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.
Open this post in threaded view
|

## 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.comPostgreSQL Support for Secure Enterprises Consulting, Training, & Open Source Development
Open this post in threaded view
|

## Re: Need to omit time during weekends from age calculations

 Joe Conway <[hidden email]> 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
Open this post in threaded view
|