> 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
> Example (please pardon the non-timestamp vals here...)
> would give me '1 day'.
> 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?
CREATE OR REPLACE FUNCTION nonweekendhours(startts timestamptz, endts
RETURNS interval AS $$
(SUM(case when extract(dow from g.ts) > 0
and extract(dow from g.ts) < 6 then
0 end) || ' hours')::interval
FROM generate_series(startts, endts - '1 hour'::interval,'1 hour') AS
$$ LANGUAGE sql;
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
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.
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.