I wrote this query that suppose to count the working days in a month but I get the following error:

[42883] ERROR: operator does not exist: double precision + interval Hinweis: No operator matches the given name and argument types. You might need to add explicit type casts. Position: 305

The date is a timestamp and this is the function below serves as the denominator to normalize sales:

(count(((date_part('month',date))+ Interval '1 month'- Interval '1 day')-(date_part('month',date)))
           filter (where date_part('dow', date) not in (6,0)))

In the end I will have:

select date_part('month', date) as month, 
sum(sales)/denominator as Monthly_noralized_sales
from sales;
  • I have no idea what (date_part('month',date) + Interval '1 month'- Interval '1 day') - (date_part('month',date)) is supposed to do. Additionally it seems pointless to pass that expression to the count() aggregate as it is essentially the same as count(date) - i.e. it will include all rows where date is not null. Which is also achieved by your filter anyway as that will also exclude rows where date is null. So it seems you can simplify that to count(*) filter (where date_part(..) not in (..)) – a_horse_with_no_name Sep 3 '20 at 9:36

I'm not sure if that is what you're trying to achieve, but to count working days you need a range of dates.

IMHO the best way to deal with dates is by using a calendar table. Keep in mine that you need to add holidays and days off to your calendar. You can find a lot of samples googling a bit.

If you don't have/want to use a calendar table, you can take advantage of generate_series function.

create table t (dt timestamp);
insert into t values ('20200110'),('20200203'),('20200715');

    date_part('month', dts) mt,
    count(*) as wd
        generate_series(date_trunc('month', dt)::date,
                        (date_trunc('month', dt)::date 
                                    + interval '1 month - 1 day')::date,
                        interval '1 day') dts
        t) t2
    date_part('dow', dts) <> 6
group by
    date_part('month', dts);
mt | wd
:- | -:
7  | 27
1  | 27
2  | 24

db<>fiddle here

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.