The TO_CHAR function formats a timestamp into a string using a given format.


The syntax for using the TO_CHAR function is as follows:

TO_CHAR(timestamp, format_string)

Parameters in the syntax include:

  • timestamp: The TIMESTAMP or TIMESTAMP WITH TIMEZONE to be formatted to string.

  • format: The format of the input string.


Format string supports following template patterns (can be lowercase):

YYYYYear (1-9999)
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

❌ Limitations

  • 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 does not match any pattern will be preserved in the result string.


Case 1: Timestamp with Microseconds

The query aims to format a timestamp to display the year, month, day, hour, minute, second, and microseconds (YYYY-MM-DD HH24:MI:SS.US).

SELECT TO_CHAR('2012-03-04 05:06:07.080910'::timestamp, 'YYYY-MM-DD HH24:MI:SS.US');

The output shows the timestamp ‘2012-03-04 05:06:07.080910’.

 2012-03-04 05:06:07.080910

Case 2: Time with Milliseconds and Meridiem

This query format a timestamp in a 12-hour clock format with the meridiem indicator (p.m).

SELECT TO_CHAR('2012-03-04 05:06:07.080910'::timestamp, 'HH12-MI-SS.MS p.m.');

The output shows the time 5:06:07 a.m. with milliseconds.

 05-06-07.080 a.m.

Case 3: Time with Meridiem Indicator

This query format a timestamp to display time in a 12-hour clock format along with the meridiem indicator (AM).

SELECT TO_CHAR('2012-03-04 17:06:07.080910'::timestamp, 'HH-MI-SS AM');

The output presents the time ‘17:06:07’ in the format ‘HH-MI-SS AM’: 5:06:07 PM.

 05-06-07 PM

Case 4: Timestamp with Text

This example adds custom text to the timestamp output.

SELECT TO_CHAR('2012-03-04 17:06:07.080910'::timestamp, 'Text "HH24-MI-SS" {HH24-MI-SS}');

It will return the output below.

 Text HH24-MI-SS {17-06-07}