Options
Truncating a Timestamp
Jim_Knicely
- Select Field - Administrator
The built-in Vertica function DATE_TRUNC truncates date and time values to the specified precision. The return value is the same data type as the input value. All fields that are less than the specified precision are set to 0, or to 1 for day and month.
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 in 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/9.2.x/HTML/Content/Authoring/SQLReferenceManual/Functions/Date-Time/DATE_TRUNC.htm
Have fun!
0