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

Find the Number of Days Passed and Remaining in the Relative Year

Jim Knicely authored this post.

Although there aren’t any specific functions that return the number of days that have passed and that are remaining in a given year, you can combine a few of Vertica’s built-in date functions to find these numbers.

Example:

dbadmin=> SELECT sysdate                                                                 "Today",
dbadmin->        DATEDIFF(day, TRUNC(sysdate, 'YY'), TRUNC(sysdate))                     "# of days that have passed in current year",
dbadmin->        DATEDIFF(day, TRUNC(sysdate), (ADD_MONTHS(TRUNC(sysdate, 'YY'), 12)-1)) "# of days remaining in current year";
           Today            | # of days that have passed in current year | # of days remaining in current year
----------------------------+--------------------------------------------+-------------------------------------
 2018-08-06 11:56:03.111593 |                                        217 |                                 147
(1 row)

You can encapsulate the date logic above into several user-defined functions that can be called later!

dbadmin=> CREATE OR REPLACE FUNCTION days_passed_current_year (x TIMESTAMP) RETURN INT AS
dbadmin-> BEGIN
dbadmin->   RETURN DATEDIFF(day, TRUNC(x, 'YY'), TRUNC(x));
dbadmin-> END;
CREATE FUNCTION

dbadmin=> CREATE OR REPLACE FUNCTION days_remaining_current_year (x TIMESTAMP) RETURN INT AS
dbadmin-> BEGIN
dbadmin->   RETURN DATEDIFF(day, TRUNC(x), (ADD_MONTHS(TRUNC(x, 'YY'), 12)-1));
dbadmin-> END;
CREATE FUNCTION

dbadmin=> SELECT x                              AS "Some Date",
dbadmin->        days_passed_current_year(x)    AS "# of days that have passed in the relative year",
dbadmin->        days_remaining_current_year(x) AS "# of days remaining in the relative year"
dbadmin->   FROM (SELECT '2018-JAN-01 08:00'::TIMESTAMP AS x
dbadmin(>          UNION ALL
dbadmin(>         SELECT '2018-DEC-31 08:00'::TIMESTAMP
dbadmin(>          UNION ALL
dbadmin(>         SELECT '2017-SEP-19 08:00'::TIMESTAMP) foo;
      Some Date      | # of days that have passed in the relative year | # of days remaining in the relative year
---------------------+-------------------------------------------------+------------------------------------------
 2018-01-01 08:00:00 |                                               0 |                                      364
 2018-12-31 08:00:00 |                                             364 |                                        0
 2017-09-19 08:00:00 |                                             261 |                                      103
(3 rows)

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.