如何找到本年已过天数和剩余天数

Vertica其实没有直接的函数，返回本年的已过天数和剩余天数。但是你需利用日期函数可以巧妙的完成上述功能，下面我们来看下如何实现吧~

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)

另外，你也可以将此功能写成一个函数，工其他开发人员调用。代码如下：

 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)

是不是很简单，赶快自己试试吧！