Special Date/Time Formatting: Quick Tip

Posted October 15, 2018 by Phil Molea, Sr. Information Developer, Vertica

High angle view of Beijing Guomao.
Jim Knicely authored this tip.

Vertica supports several special date/time values for our convenience. All of these values need to be written in single quotes when used as constants in SQL statements.

My favorite is ALLBALLS which is named so because the time digits look like balls (I had to Google that).

Example: dbadmin=> SELECT timestamp 'today' midnight_today, dbadmin-> timestamp 'yesterday' midnight_yesterday, dbadmin-> timestamp 'tomorrow' midnight_tomorrow, dbadmin-> time 'allballs' midnight_utc_time; midnight_today | midnight_yesterday | midnight_tomorrow | midnight_utc_time ---------------------+---------------------+---------------------+------------------- 2018-10-15 00:00:00 | 2018-10-14 00:00:00 | 2018-10-16 00:00:00 | 00:00:00 (1 row) Helpful link:


Have fun!