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.
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.
Note
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
Note
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)