Truncating a Timestamp

Jim_KnicelyJim_Knicely Administrator, Moderator, Employee, Registered User, VerticaExpert

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!

Sign In or Register to comment.