Special Date/Time Formatting

Jim_KnicelyJim_Knicely Administrator, Moderator, Employee, Registered User, VerticaExpert
edited October 15 in Vertica Tips

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 links:
https://www.vertica.com/docs/9.1.x/HTML/index.htm#Authoring/SQLReferenceManual/LanguageElements/Expressions/DateTimeExpressions.htm

Sign In or Register to comment.