Truncating a Timestamp

Posted April 5, 2019 by Jim Knicely, Big Data Solutions Architect, Vertica

Shot of two businesswomen using a digital tablet together during a collaboration at work
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 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!