​
Overview

The TO_TIMESTAMP() function converts a string into a timestamp based on the provided format. It returns a TIMESTAMP WITH TIME ZONE type.

​
Syntax

The syntax for using the TO_TIMESTAMP() function is as follows:

SELECT TO_TIMESTAMP('source', 'format'');

Let’s analyze the above syntax:

  • source: The date/time value to be converted. The value type is TIMESTAMP (YYYY-MM-DD HH:MM:SS).

  • format: The format of the input string.

​
Format

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

PatternDescriptionDetail
YYYYYear (1-9999)- The lowest possible value is 1 AD
- 0001 is 1
- 1 is 1
MMMonth number (1–12)- Up to 2 digits
- 01 is 1
- 1 is 1
DDDay of month (1–31)- Up to 2 digits
- 01 is 1
- 1 is 1
HHHour of day (1–12)- Up to 2 digits
- 01 is 1
- 1 is 1
HH12Hour of day (1–12)- Up to 2 digits
- 01 is 1
- 1 is 1
HH24Hour of day (0–23)- Up to 2 digits
- 01 is 1
- 1 is 1
MIMinute (0–59)- Up to 2 digits
- 01 is 1
- 1 is 1
SSSecond (0–59)- Up to 2 digits
- 01 is 1
- 1 is 1
MSMillisecond (0–999)- Up to 3 digits
- 001 is 1 millisecond
- 1 is 100 milliseconds
USMicrosecond (0–999999)- Up to 6 digits
- 000001 is 1 microsecond
- 1 is 100000 milliseconds
AM, am, PM or pmMeridiem indicatorWithout periods
A.M., a.m., P.M. or p.m.Meridiem indicatorWith periods

​
Examples

​
Case #1: Timestamp into YYYY-MM-DD HH24:MI

The TO_TIMESTAMP() function converts the provided string into a timestamp with the format YYYY-MM-DD HH24:MI.

select TO_TIMESTAMP('2020-03-04 14:30', 'YYYY-MM-DD HH24:MI');

The final output will be a timestamp with a timezone.

        to_timestamp          
-------------------------------
 2020-03-04 14:30:00.000000+00

​
Case #2: Timestamp into MM-DD HH12:MI

The TO_TIMESTAMP() function converts the provided string into a timestamp with the format MM-DD HH12:MI.

select TO_TIMESTAMP('3-04 02:30', 'MM-DD HH12:MI');

The final output will be a timestamp with a timezone.

       to_timestamp        
----------------------------
 1-03-04 02:30:00.000000+00

​
Case #3: Timestamp into YYYY-MM HH12:MI(AM/PM)

The TO_TIMESTAMP()` function converts the provided string into a timestamp with the format YYYY-MM HH12:MI with meridiem indicator (AM/PM).

Request 1

select TO_TIMESTAMP('2020-02 12:30AM', 'YYYY-MM HH12:MIPM');

Request 2

select TO_TIMESTAMP('2020-02 12:30AM', 'YYYY-MM HH:MIAM');

The final output of both requests will have the same result. It changes the time into a 12-hour format, resulting in 12:30 being adjusted to 00:30.

         to_timestamp          
-------------------------------
 2020-02-01 00:30:00.000000+00

​
Case #4: Timestamp into YYYY-MM-DD HH24:MI:SS.MS.US

The TO_TIMESTAMP() function converts the provided string into a timestamp with YYYY-MM-DD HH24:MI:SS.MS.US format.

select TO_TIMESTAMP('1960-01-31 15:12:02.020.001230', 'YYYY-MM-DD HH24:MI:SS.MS.US');

The final output will be a timestamp with milliseconds and microseconds.

        to_timestamp          
-------------------------------
 1960-01-31 15:12:02.021230+00

​
Case #5: Timestamp into YYYY-MM-DD HH24:MI:SS.MS

The TO_TIMESTAMP() function converts the provided string into a timestamp with YYYY-MM-DD HH24:MI:SS.MS format.

select TO_TIMESTAMP('1960-01-31 15:12:02.02', 'YYYY-MM-DD HH24:MI:SS.MS');

The final output will be a timestamp with milliseconds.

        to_timestamp          
-------------------------------
 1960-01-31 15:12:02.020000+00
TO_CHAR from TimestampUNIX_MICROS