TO_CHAR from Timestamp

​

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):

​

❌ 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}