Counting Days, Week Days and Weekend Days Across the Years: Quick Tip

Jim Knicely authored this post. 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!