The Vertica Forum recently got a makeover! Let us know what you think by filling out this short, anonymous survey.

Counting Days, Week Days and Weekend Days Across the Years

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!

Sign In or Register to comment.

Can't find what you're looking for? Search the Vertica Documentation, Knowledge Base, or Blog for more information.