The DATE_TRUNC Function

Moderator: NorbertKrupa

Post Reply
User avatar
JimKnicely
Site Admin
Site Admin
Posts: 1825
Joined: Sat Jan 21, 2012 4:58 am
Contact:

The DATE_TRUNC Function

Post by JimKnicely » Fri Aug 10, 2012 1:03 pm

The new Vertica 6.0 DATE_TRUNC function can be used to truncate a date to a specific precision.

Examples:

Code: Select all

dbadmin=> \x
Expanded display is on.

dbadmin=> select sysdate,
dbadmin->        date_trunc('year', sysdate)   trunc_year,
dbadmin->        date_trunc('month', sysdate)  trunc_month,
dbadmin->        date_trunc('week', sysdate)   trunc_week,
dbadmin->        date_trunc('day', sysdate)    trunc_day,
dbadmin->        date_trunc('hour', sysdate)   trunc_hour,
dbadmin->        date_trunc('minute', sysdate) trunc_minute,
dbadmin->        date_trunc('second', sysdate) trunc_second;
-[ RECORD 1 ]+--------------------------
sysdate      | 2012-07-03 11:46:23.38257
trunc_year   | 2012-01-01 00:00:00
trunc_month  | 2012-07-01 00:00:00
trunc_week   | 2012-07-02 00:00:00
trunc_day    | 2012-07-03 00:00:00
trunc_hour   | 2012-07-03 11:00:00
trunc_minute | 2012-07-03 11:46:00
trunc_second | 2012-07-03 11:46:23

dbadmin=> select date_trunc('year', sysdate) - 1 last_day_of_last_year;
last_day_of_last_year
-----------------------
2011-12-31 00:00:00
(1 row)
Anyone remember the whole debate about which date was the actual 1st day of the 21st century? Was it 01/01/2000 or 01/01/2001? Now that we have the date_trunc function the mystery is solved!

Code: Select all

dbadmin=> select sysdate,
dbadmin->        date_part('century', sysdate)  current_century,
dbadmin->        date_trunc('century', sysdate) first_date_of_the_current_century;
          sysdate          | current_century | first_date_of_the_current_century
---------------------------+-----------------+-----------------------------------
2012-07-03 14:22:07.99621 |              21 | 2001-01-01 00:00:00
Have fun!
Jim Knicely

Image

Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.

Post Reply

Return to “Vertica Tips, Lessons and Examples”