Truncating a Timestamp
Jim_Knicely
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