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 isTIMESTAMP(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