Template Patterns for Date/Time Formatting
In an output template string (for TO_CHAR), certain patterns are recognized and replaced with appropriately formatted data from the value to format. Any text that is not a template pattern is copied verbatim. Similarly, in an input template string (for anything other than TO_CHAR), template patterns identify the parts of the input data string to look at and the values to find there.
Vertica uses the ISO 8601:2004 style for date/time fields in Vertica log files. For example:
2020-03-25 05:04:22.372 Init Session:0x7f8fcefec700-a000000013dcd4 [Txn] <INFO> Begin Txn: a000000013dcd4 'read role info'
Certain modifiers can be applied to any template pattern to alter its behavior, as described in Template Pattern Modifiers for Date/Time Formatting.
| Pattern | Description |
|---|---|
| HH |
Hour of day (00-23) |
| HH12 |
Hour of day (01-12) |
| HH24 |
Hour of day (00-23) |
| MI |
Minute (00-59) |
| SS |
Second (00-59) |
| MS |
Millisecond (000-999) |
| US |
Microsecond (000000-999999) |
| SSSS |
Seconds past midnight (0-86399) |
| AM A.M. PM P.M. |
Meridian indicator (uppercase) |
| am a.m. pm p.m. |
Meridian indicator (lowercase) |
| Y YYY |
Year (4 and more digits) with comma |
| YYYY |
Year (4 and more digits) |
| YYY |
Last 3 digits of year |
| YY |
Last 2 digits of year |
| Y |
Last digit of year |
| IYYY |
ISO year (4 and more digits) |
| IYY |
Last 3 digits of ISO year |
| IY |
Last 2 digits of ISO year |
| I |
Last digits of ISO year |
| BC B.C. AD A.D. |
Era indicator (uppercase) |
| bc b.c. ad a.d. |
Era indicator (lowercase) |
| MONTH |
Full uppercase month name (blank-padded to 9 chars) |
| Month |
Full mixed-case month name (blank-padded to 9 chars) |
| month |
Full lowercase month name (blank-padded to 9 chars) |
| MON |
Abbreviated uppercase month name (3 chars) |
| Mon |
Abbreviated mixed-case month name (3 chars) |
| mon |
Abbreviated lowercase month name (3 chars) |
| MM |
Month number (01-12) |
| DAY |
Full uppercase day name (blank-padded to 9 chars) |
| Day |
Full mixed-case day name (blank-padded to 9 chars) |
| day |
full lowercase day name (blank-padded to 9 chars) |
| DY |
Abbreviated uppercase day name (3 chars) |
| Dy |
Abbreviated mixed-case day name (3 chars) |
| dy |
Abbreviated lowercase day name (3 chars) |
| DDD |
Day of year (001-366) |
| DD |
Day of month (01-31) for TIMESTAMP For INTERVAL, DD is day of year (001-366) because day of month is undefined. |
| D |
Day of week (1-7; Sunday is 1) |
| W |
Week of month (1-5) (The first week starts on the first day of the month.) |
| WW |
Week number of year (1-53) (The first week starts on the first day of the year.) |
| IW |
ISO week number of year (The first Thursday of the new year is in week 1.) |
| CC |
Century (2 digits) |
| J |
Julian Day (days since January 1, 4712 BC) |
| Q |
Quarter |
| RM |
Month in Roman numerals (I-XII; I=January) (uppercase) |
| rm |
Month in Roman numerals (i-xii; i=January) (lowercase) |
| TZ |
Time-zone name (uppercase) |
| tz |
Time-zone name (lowercase) |
Examples
Use TO_TIMESTAMP to convert an expression using the pattern 'YYY MON':
=> SELECT TO_TIMESTAMP('2017 JUN', 'YYYY MON');
TO_TIMESTAMP
---------------------
2017-06-01 00:00:00
(1 row)
Use TO_DATE to convert an expression using the pattern 'YYY-MMDD':
=> SELECT TO_DATE('2017-1231', 'YYYY-MMDD');
TO_DATE
------------
2017-12-31
(1 row)