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!
