PostgreSQL: Calculate Business Days Between Dates

When you’re tracking SLAs, estimating project timelines, or calculating delivery windows, weekends and holidays matter. You can’t tell a customer “your issue will be resolved in 5 days” if 2 of those days are Saturday and Sunday. I’ve built support ticket systems where business day calculations are critical—they determine escalation timing and SLA compliance.

The challenge is that PostgreSQL’s date arithmetic doesn’t inherently understand “business days”—you need to generate all the days in your range and filter out weekends (and optionally holidays). Once you master this pattern, you’ll use it constantly in business applications.

Quick Answer: Use Generate Series with Filtering

To count business days between dates:

SELECT
    start_date,
    end_date,
    COUNT(*)::INT as business_days
FROM (
    SELECT
        '2026-02-21'::DATE as start_date,
        '2026-02-28'::DATE as end_date
) dates,
LATERAL generate_series(start_date, end_date, '1 day'::INTERVAL) AS d
WHERE EXTRACT(DOW FROM d) NOT IN (0, 6)  -- Exclude Sunday(0), Saturday(6)
GROUP BY start_date, end_date;

Returns business days excluding weekends.

How It Works

Generate all days in range, count only weekdays (Monday-Friday). DOW extraction: Sunday=0, Saturday=6. Filter those out.

Different Approaches & When to Use Them

Approach 1: Weekdays Only (Simplest)

WITH day_series AS (
    SELECT generate_series(start_date, end_date, '1 day'::INTERVAL)::DATE as day
    FROM (SELECT '2026-02-01'::DATE as start_date, '2026-02-28'::DATE as end_date)
)
SELECT COUNT(*)::INT as business_days
FROM day_series
WHERE EXTRACT(DOW FROM day) NOT IN (0, 6);

Approach 2: With Holiday Exclusion

WITH day_series AS (
    SELECT generate_series(start_date, end_date, '1 day'::INTERVAL)::DATE as day
    FROM (SELECT DATE '2026-02-01', DATE '2026-02-28')
)
SELECT COUNT(*)::INT as business_days
FROM day_series
WHERE EXTRACT(DOW FROM day) NOT IN (0, 6)
  AND day NOT IN (SELECT holiday_date FROM holidays);  -- Exclude specific holidays

Approach 3: Create Reusable Function

CREATE OR REPLACE FUNCTION count_business_days(start_date DATE, end_date DATE)
RETURNS INT AS $$
SELECT COUNT(*)::INT
FROM generate_series(start_date, end_date, '1 day'::INTERVAL)::DATE day
WHERE EXTRACT(DOW FROM day) NOT IN (0, 6)
$$ LANGUAGE SQL;

-- Usage:
SELECT count_business_days('2026-02-01', '2026-02-28');

Best for: Reuse across queries.

Approach 4: Mathematical Approach (Faster)

SELECT
    start_date,
    end_date,
    (
        (DATE_PART('day', end_date) - DATE_PART('day', start_date)) +
        (DATE_PART('isodow', end_date) - DATE_PART('isodow', start_date)) * 7 / 5
    )::INT as approx_business_days
FROM (SELECT DATE '2026-02-01' as start_date, DATE '2026-02-28' as end_date);

Approximation using math.

Real-World Example: SLA Response Time

Track business days for ticket response:

SELECT
    ticket_id,
    created_at::DATE as created_date,
    resolved_at::DATE as resolved_date,
    (
        SELECT COUNT(*)::INT
        FROM generate_series(created_at::DATE, resolved_at::DATE, '1 day'::INTERVAL)::DATE day
        WHERE EXTRACT(DOW FROM day) NOT IN (0, 6)
    ) as business_days_to_resolve,
    CASE
        WHEN (
            SELECT COUNT(*)
            FROM generate_series(created_at::DATE, resolved_at::DATE, '1 day'::INTERVAL)::DATE day
            WHERE EXTRACT(DOW FROM day) NOT IN (0, 6)
        ) <= 2
        THEN 'Within SLA'
        ELSE 'Exceeded SLA'
    END as sla_status
FROM tickets
WHERE resolved_at IS NOT NULL;

Performance Notes

Generate_series approach is clear but slower for large date ranges (>1000 days). Mathematical approach is faster for estimation.

FAQ

Q: How do I exclude specific holidays? A: Add AND day NOT IN (SELECT holiday_date FROM holidays_table).

Q: Which countries have different weekend definitions? A: Most use Sat/Sun. Some use Fri/Sat. Adjust DOW conditions accordingly.

Q: How do I include Saturday? A: Remove 6 from the IN clause: WHERE EXTRACT(DOW FROM day) NOT IN (0).

Q: Performance for very long date ranges? A: Generate_series is slow. Use mathematical approach or recursive CTEs.

Q: Can I get a breakdown by week? A: Use DATE_TRUNC(‘week’, …) before grouping.

Wrapping Up

Count business days using generate_series filtered for weekdays. For accuracy with holidays, exclude them explicitly. For performance, use mathematical approximation.