Select COUNT of days between two dates except weekends

I am trying to get the number of days between two different dates, except the weekends.

I don't know how to achieve this result.

Assuming that by "weekend" you mean Saturday and Sunday, this can be even simpler:

SELECT count(*) AS count_days_no_weekend
FROM   generate_series(timestamp '2014-01-01'
                     , timestamp '2014-01-10'
                     , interval  '1 day') the_day
WHERE  extract('ISODOW' FROM the_day) < 6;

-
You don't need an extra subquery level for generate_series(). SRF (set returning functions), also referred to as "table-functions", can be used just like tables in the FROM clause.

-
Note in particular that generate_series() includes the upper bound in the output, as long as a full interval (3rd parameter) fits. The upper bound is only excluded if the last interval would be truncated, which is not the case with full days.

-
With the pattern ISODOW for EXTRACT(), Sundays are reported as 7, according to the ISO standard. Allows for a simpler WHERE condition.

-
Rather call generate_series() with timestamp input. Here is why:

• Generating time series between two dates in PostgreSQL

-
count(*) is slightly shorter and faster than count(the_day), doing the same in this case.

To exclude lower and / or upper bound, add / subtract 1 day accordingly. Typically, you might include the lower and exclude the upper bound:

SELECT count(*) AS count_days_no_weekend
FROM   generate_series(timestamp '2014-01-01'
                     , timestamp '2014-01-10' - interval '1 day'
                     , interval '1 day') the_day
WHERE  extract('ISODOW' FROM the_day) < 6;

StackExchange Database Administrators Q#55971, answer score: 16