
Example:
dbadmin=> \x
Expanded display is on.
dbadmin=> SELECT sysdate,
dbadmin-> DATE_TRUNC('MILLENNIUM', sysdate) AS BY_MILLENNIUM,
dbadmin-> DATE_TRUNC('CENTURY', sysdate) AS BY_CENTURY,
dbadmin-> DATE_TRUNC('DECADE', sysdate) AS BY_DECADE,
dbadmin-> DATE_TRUNC('YEAR', sysdate) AS BY_YEAR,
dbadmin-> DATE_TRUNC('QUARTER', sysdate) AS BY_QUARTER,
dbadmin-> DATE_TRUNC('MONTH', sysdate) AS BY_MONTH,
dbadmin-> DATE_TRUNC('DAY', sysdate) AS BY_DAY,
dbadmin-> DATE_TRUNC('HOUR', sysdate) AS BY_HOUR,
dbadmin-> DATE_TRUNC('MINUTE', sysdate) AS BY_MINUTE,
dbadmin-> DATE_TRUNC('SECOND', sysdate) AS BY_SECOND,
dbadmin-> DATE_TRUNC('MILLISECONDS', sysdate) AS BY_MILLISECONDS,
dbadmin-> DATE_TRUNC('MICROSECONDS', sysdate) AS BY_MICROSECONDS;
-[ RECORD 1 ]---+---------------------------
sysdate | 2019-04-04 16:34:32.705186
BY_MILLENNIUM | 2001-01-01 00:00:00
BY_CENTURY | 2001-01-01 00:00:00
BY_DECADE | 2010-01-01 00:00:00
BY_YEAR | 2019-01-01 00:00:00
BY_QUARTER | 2019-04-01 00:00:00
BY_MONTH | 2019-04-01 00:00:00
BY_DAY | 2019-04-04 00:00:00
BY_HOUR | 2019-04-04 16:00:00
BY_MINUTE | 2019-04-04 16:34:00
BY_SECOND | 2019-04-04 16:34:32
BY_MILLISECONDS | 2019-04-04 16:34:32.705
BY_MICROSECONDS | 2019-04-04 16:34:32.705186
So did the current Millennium begin on January 1, 2000 or January 1, 2001? Let’s ask Vertica!
dbadmin=> SELECT DATE_TRUNC('MILLENNIUM', '01/01/2000'::TIMESTAMP),
dbadmin-> DATE_TRUNC('MILLENNIUM', '01/01/2001'::TIMESTAMP);
DATE_TRUNC | DATE_TRUNC
---------------------+---------------------
1001-01-01 00:00:00 | 2001-01-01 00:00:00
(1 row)
Helpful Link:
https://www.vertica.com/docs/latest/HTML/Content/Authoring/SQLReferenceManual/Functions/Date-Time/DATE_TRUNC.htm
Have fun!