20,000 Vertica Timestamp Format Examples

You will love this blog because date, timestamp functions and formatting differ dramatically between databases, and Vertica handles them uniquely, but I have covered everything you need to know about formatting with brilliant examples and clear and concise explanations.

All of these examples have come from my books and training classes. Please do me a favor and tell your training coordinator that you know the best technical trainer in the world. Ask them to hire me to train at your company, either on-site or with a virtual class. They can see our classes, outlines, and a sample of my teaching at this link on our website.

https://coffingdw.com/education/

Below are some great examples for formatting a Vertica timestamp to get you a great start to understanding how Vertica formats dates and timestamps.

YYYY: Four-digit year.
YY: Two-digit year.
MM: Month (01-12).
DD: Day of the month (01-31) with leading zero.
HH: Hour (01-12).
HH12: Hour 01-12).
HH24: Hour (00-23).
MI: Minute (00-59).
SS: Second (00-59).
MS: Milliseconds.
US: Microseconds.
Month: Name of month capitalized and padded up to nine characters.
Mon: Abbreviated month name (e.g., Jan, Feb).
CC: Century.
DAY: Name of the day of the week in all caps.
Dy: Abbreviated day of the week capitalized (e.g., Sun, Mon).
Day: Name of the day of the week capitalized.
AM|PM: AM or PM).
am|pm: am or pm).

I bet you didn’t know you could use the TO_CHAR function in the WHERE and AND clauses to filter rows. The query below only returns rows where the customer_timestamp has a month of December, a day of Saturday, a minute of 54, a second of 34, and the first three digits of the microsecond (millisecond) is 000. Also notice we use the alias dow in the SELECT list and again in the WHERE clause.

In the table below you can scroll to see 20,000 different Vertica timestamp format examples.