This page contains the full list of template patterns and template pattern modifiers that can be used when formatting date and times in PostgreSQL.
Template Patterns
The following table lists all template patterns that can be used in your format strings when formatting date and times in Postgres.
Pattern | Description |
---|---|
HH | Hour of day (01-12). |
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 , am , PM or pm | Meridiem indicator (without periods). |
A.M. , a.m. , P.M. or p.m. | Meridiem indicator (with periods). |
Y,YYY | Year (4 or more digits) with comma. |
YYYY | Year (4 or more digits). |
YYY | Last 3 digits of year. |
YY | Last 2 digits of year. |
Y | Last digit of year. |
IYYY | ISO 8601 week-numbering year (4 or more digits). |
IYY | Last 3 digits of ISO 8601 week-numbering year. |
IY | Last 2 digits of ISO 8601 week-numbering year. |
I | Last digit of ISO 8601 week-numbering year. |
BC , bc , AD or ad | Era indicator (without periods). |
B.C. , b.c. , A.D. or a.d. | Era indicator (with periods). |
MONTH | Full upper case month name (blank-padded to 9 chars). |
Month | Full capitalized month name (blank-padded to 9 chars). |
month | Full lower case month name (blank-padded to 9 chars). |
MON | Abbreviated upper case month name (3 chars in English, localized lengths vary). |
Mon | Abbreviated capitalized month name (3 chars in English, localized lengths vary). |
mon | Abbreviated lower case month name (3 chars in English, localized lengths vary). |
MM | Month number (01-12). |
DAY | Full upper case day name (blank-padded to 9 chars). |
Day | Full capitalized day name (blank-padded to 9 chars). |
day | Full lower case day name (blank-padded to 9 chars). |
DY | Abbreviated upper case day name (3 chars in English, localized lengths vary). |
Dy | Abbreviated capitalized day name (3 chars in English, localized lengths vary). |
dy | Abbreviated lower case day name (3 chars in English, localized lengths vary). |
DDD | Day of year (001-366). |
IDDD | Day of ISO 8601 week-numbering year (001-371; day 1 of the year is Monday of the first ISO week). |
DD | Day of month (01-31). |
D | Day of the week, Sunday (1 ) to Saturday (7 ). |
ID | ISO 8601 day of the week, Monday (1 ) to Sunday (7 ). |
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 | Week number of ISO 8601 week-numbering year (01-53; the first Thursday of the year is in week 1). |
CC | Century (2 digits) (the twenty-first century starts on 2001-01-01). |
J | Julian Day (integer days since November 24, 4714 BC at midnight UTC). |
Q | Quarter. |
RM | Month in upper case Roman numerals (I-XII; I=January). |
rm | Month in lower case Roman numerals (i-xii; i=January). |
TZ | Upper case time-zone abbreviation (only supported in to_char ). |
tz | Lower case time-zone abbreviation (only supported in to_char ). |
TZH | Time-zone hours. |
TZM | Time-zone minutes. |
OF | Time-zone offset from UTC (only supported in to_char ). |
Template Pattern Modifiers
The following modifiers can be applied to the above template patterns to alter the result.
Modifier | Description |
---|---|
FM prefix | Fill mode (suppress leading zeroes and padding blanks). |
TH suffix | Upper case ordinal number suffix. |
th suffix | Lower case ordinal number suffix. |
FX prefix | Fixed format global option. |
TM prefix | Translation mode (print localized day and month names based on lc_time ). |
SP suffix | Spell mode (not implemented). |
Note that some of these are added as a prefix to the template pattern and others are added as a suffix.
All of the above template patterns and modifiers are based on the Postgres documentation, which includes usage notes. To see the usage notes, click on that link and scroll down. The usage notes are under the template modifiers.
Example
Here’s a quick example to demonstrate how it works.
SELECT to_char(date '2020-12-01', 'Dth');
Result:
3rd
In this case, the D
was for the day of the month (3), and the th
was used to add the ordinal indicator (in this case, rd).