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)