Template Patterns for Date/Time Formatting
In an output template string (for TO_CHAR
), there are certain patterns that are recognized and replaced with appropriately-formatted data from the value to be formatted. 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 be looked at and the values to be found there.
Note: Vertica uses the ISO 8601:2004 style for date/time fields in Vertica *.log
files. For example, 2008-09-16 14:40:59.123 TM Moveout:0x2aaaac002180 [Txn] <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 or A.M. or PM or P.M. |
Meridian indicator (uppercase) |
am or a.m. or pm or 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 or B.C. or AD or A.D. |
Era indicator (uppercase) |
bc or b.c. or ad or 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)