​
Overview

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

​
Syntax

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

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

PatternDescription
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.

​
Examples

​
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’.

          to_char           
----------------------------
 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.

      to_char      
-------------------
 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.

  to_char   
-------------
 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.

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