​
Overview

The TO_CHAR function formats various data types, including date/time, integer, float point and numeric into a formatted string.

​
Syntax

The syntax for using the TO_CHAR function is as follows:

TO_CHAR(timestamp, format_string)

​
Arguments

  • timestamp: TIMESTAMP or TIMESTAMP WITH TIMEZONE value to be formatted
  • format: format of the output string

​
Supported Formats

The string format supports the following template patterns (case insensitive):

PatternDescription
YYYYYear (1-9999)
MMMonth number (01–12)
DDDay of month (01–31)
HHHour of day (1–12)
HH12Hour of day (1–12)
HH24Hour of day (0–23)
MIMinute (0–59)
SSSecond (0–59)
MSMillisecond (0–999)
USMicrosecond (0–999999)
AM, am, PM or pmMeridiem indicator without periods
A.M., a.m., P.M. or p.m.Meridiem indicator with periods

​
General Restrictions

  • All text inside double quote "{text}" will not be considered a pattern
  • The quote character (") will not appear in the result string
  • Any text that is not a template pattern is simply copied verbatim i.e. preserved in the result string

​
Interval Overflow Restrictions

Interval overflow occurs when an operation involving interval values exceeds the maximum limits of the interval data type, resulting in an error or unexpected behavior. This can happen when adding, subtracting or multiplying interval values that lead to a representation that goes beyond the allowable range for any of its components i.e. years, months, days, hours, minutes and seconds. When executing the TO_CHAR function for intervals, it is important to be aware of the following overflow restrictions:

ConversionSource ComponentTarget Component
Days to MonthsDaysMonths
Hours to DaysHoursDays
Seconds to DaysSecondsDays

All in all, for intervals the date overflow doesn’t apply (units smaller than an hour can only overflow into hours, but not into days and so on), any excess units will not carry over to the next larger unit.

​
Examples

​
Intervals

This query converts an interval and displays it in a specified string format:

SELECT TO_CHAR('25 months'::INTERVAL,'"YEAR:" YYYY "MONTH:" MM') AS FORMATTED_INTERVAL;

Here are the outputs for the queries presented above:

          FORMATTED_INTERVAL                
---------------------------------------
YEAR: 0002 MONTH: 01

​
Timestamps

This query retrieves the current timestamp and displays it in a specified string format:

SELECT TO_CHAR(CURRENT_TIMESTAMP(), '"YEAR:" YYYY "MONTH:" MM "DAY:" DD') AS FORMATTED_TIMESTAMP;

Here are the outputs for the queries presented above:

          FORMATTED_TIMESTAMP                
---------------------------------------
YEAR:2025 MONTH:01 DAY:01