Counting Days, Week Days and Weekend Days Across the Years

[Deleted User]

Jim Knicely authored this tip.

By combing Vertica’s built-in date formatting functions and the TIMESERIES clause, it’s easy to find the total number of days, total number of week days (Monday – Friday), and the total number of weekend days (Saturday – Sunday) that occur within a multi-year range (i.e., 2016 – 2018), including Leap Years!

Example:

dbadmin=> SELECT EXTRACT(YEAR FROM ts) the_year,
dbadmin->        COUNT(*) AS total_days,
dbadmin->        SUM(CASE WHEN TO_CHAR(ts, 'D')::INT BETWEEN 2 AND 6 THEN 1 ELSE 0 END) AS total_week_days,
dbadmin->        SUM(CASE WHEN TO_CHAR(ts, 'D')::INT IN (1, 7) THEN 1 ELSE 0 END) AS total_weekend_days
dbadmin->   FROM (SELECT ts
dbadmin(>           FROM (SELECT '2016-01-01'::TIMESTAMP AS tm
dbadmin(>                  UNION ALL
dbadmin(>                 SELECT '2018-12-31'::TIMESTAMP AS tm) AS t TIMESERIES ts AS '1 day' OVER (ORDER BY tm)) foo
dbadmin->  GROUP BY 1
dbadmin->  ORDER BY 1;
 the_year | total_days | total_week_days | total_weekend_days
----------+------------+-----------------+--------------------
     2016 |        366 |             261 |                105
     2017 |        365 |             260 |                105
     2018 |        365 |             261 |                104
(3 rows)

dbadmin=> SELECT COUNT(*) AS total_days,
dbadmin->        SUM(CASE WHEN TO_CHAR(ts, 'D')::INT BETWEEN 2 AND 6 THEN 1 ELSE 0 END) AS total_week_days,
dbadmin->        SUM(CASE WHEN TO_CHAR(ts, 'D')::INT IN (1, 7) THEN 1 ELSE 0 END) AS total_weekend_days
dbadmin->   FROM (SELECT ts
dbadmin(>           FROM (SELECT '2016-01-01'::TIMESTAMP AS tm
dbadmin(>                  UNION ALL
dbadmin(>                 SELECT '2018-12-31'::TIMESTAMP AS tm) AS t TIMESERIES ts AS '1 day' OVER (ORDER BY tm)) foo;
 total_days | total_week_days | total_weekend_days
------------+-----------------+--------------------
       1096 |             782 |                314
(1 row)

Have fun!