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

SarahLSarahL Administrator, Moderator, Employee, Registered User, VerticaExpert, CABuser, VerticaPartners

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.