TO_TIMESTAMP

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

Pattern	Description	Detail
YYYY	Year (1-9999)	- The lowest possible value is 1 AD - 0001 is 1 - 1 is 1
MM	Month number (1–12)	- Up to 2 digits - 01 is 1 - 1 is 1
DD	Day of month (1–31)	- Up to 2 digits - 01 is 1 - 1 is 1
HH	Hour of day (1–12)	- Up to 2 digits - 01 is 1 - 1 is 1
HH12	Hour of day (1–12)	- Up to 2 digits - 01 is 1 - 1 is 1
HH24	Hour of day (0–23)	- Up to 2 digits - 01 is 1 - 1 is 1
MI	Minute (0–59)	- Up to 2 digits - 01 is 1 - 1 is 1
SS	Second (0–59)	- Up to 2 digits - 01 is 1 - 1 is 1
MS	Millisecond (0–999)	- Up to 3 digits - 001 is 1 millisecond - 1 is 100 milliseconds
US	Microsecond (0–999999)	- Up to 6 digits - 000001 is 1 microsecond - 1 is 100000 milliseconds
AM, am, PM or pm	Meridiem indicator	Without periods
A.M., a.m., P.M. or p.m.	Meridiem indicator	With 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