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)