Cloud

to_timestamp

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:

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

  • source: The date/time value to convert, provided as a text string (YYYY-MM-DD HH:MM:SS) that is parsed into a timestamp.

  • format: The format of the input string.

If the source string is NULL, to_timestamp returns NULL instead of raising an error. This behavior is compatible with PostgreSQL.

Format

Format string support these 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 microseconds.

AM, am, PM or pm

Meridiem indicator

Without periods.

A.M., a.m., P.M. or p.m.

Meridiem indicator

With periods.

Examples

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 output is a timestamp with a timezone.

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

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 output is a timestamp with a timezone.

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

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 output of both requests is the same. 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

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 output is a timestamp with milliseconds and microseconds.

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

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 output is a timestamp with milliseconds.

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