Template Patterns & Modifiers for Date/Time Formatting in PostgreSQL

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.

PatternDescription
HHHour of day (01-12).
HH12Hour of day (01-12).
HH24Hour of day (00-23).
MIMinute (00-59).
SSSecond (00-59).
MSMillisecond (000-999).
USMicrosecond (000000-999999).
SSSSSeconds past midnight (0-86399).
AMamPM or pmMeridiem indicator (without periods).
A.M.a.m.P.M. or p.m.Meridiem indicator (with periods).
Y,YYYYear (4 or more digits) with comma.
YYYYYear (4 or more digits).
YYYLast 3 digits of year.
YYLast 2 digits of year.
YLast digit of year.
IYYYISO 8601 week-numbering year (4 or more digits).
IYYLast 3 digits of ISO 8601 week-numbering year.
IYLast 2 digits of ISO 8601 week-numbering year.
ILast digit of ISO 8601 week-numbering year.
BCbcAD or adEra indicator (without periods).
B.C.b.c.A.D. or a.d.Era indicator (with periods).
MONTHFull upper case month name (blank-padded to 9 chars).
MonthFull capitalized month name (blank-padded to 9 chars).
monthFull lower case month name (blank-padded to 9 chars).
MONAbbreviated upper case month name (3 chars in English, localized lengths vary).
MonAbbreviated capitalized month name (3 chars in English, localized lengths vary).
monAbbreviated lower case month name (3 chars in English, localized lengths vary).
MMMonth number (01-12).
DAYFull upper case day name (blank-padded to 9 chars).
DayFull capitalized day name (blank-padded to 9 chars).
dayFull lower case day name (blank-padded to 9 chars).
DYAbbreviated upper case day name (3 chars in English, localized lengths vary).
DyAbbreviated capitalized day name (3 chars in English, localized lengths vary).
dyAbbreviated lower case day name (3 chars in English, localized lengths vary).
DDDDay of year (001-366).
IDDDDay of ISO 8601 week-numbering year (001-371; day 1 of the year is Monday of the first ISO week).
DDDay of month (01-31).
DDay of the week, Sunday (1) to Saturday (7).
IDISO 8601 day of the week, Monday (1) to Sunday (7).
WWeek of month (1-5) (the first week starts on the first day of the month).
WWWeek number of year (1-53) (the first week starts on the first day of the year).
IWWeek number of ISO 8601 week-numbering year (01-53; the first Thursday of the year is in week 1).
CCCentury (2 digits) (the twenty-first century starts on 2001-01-01).
JJulian Day (integer days since November 24, 4714 BC at midnight UTC).
QQuarter.
RMMonth in upper case Roman numerals (I-XII; I=January).
rmMonth in lower case Roman numerals (i-xii; i=January).
TZUpper case time-zone abbreviation (only supported in to_char).
tzLower case time-zone abbreviation (only supported in to_char).
TZHTime-zone hours.
TZMTime-zone minutes.
OFTime-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.

ModifierDescription
FM prefixFill mode (suppress leading zeroes and padding blanks).
TH suffixUpper case ordinal number suffix.
th suffixLower case ordinal number suffix.
FX prefixFixed format global option.
TM prefixTranslation mode (print localized day and month names based on lc_time).
SP suffixSpell 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).