PostgreSQL TO_TIMESTAMP

In this PostgreSQL tutorial, we will discuss the PostgreSQL TO_TIMESTAMP() function, which converts a string into a timestamp type value based on a specified format, with 11 real-time examples.

PostgreSQL TO_TIMESTAMP

PostgreSQL provides several data type formatting functions, which serve as a comprehensive set of tools to convert various data type values (such as date-time/timestamp, integer, numeric, and floating-point) into formatted strings and vice versa.

The TO_TIMESTAMP() is one of these data type formatting functions, which is used to convert a string-type value into a timestamp-type value with the specified formatting.

Syntax

The syntax is as follows:

TO_TIMESTAMP(timestamp, format)

In the above syntax,

• The timestamp is the argument that represents a string (TEXT type) with a date/time value to be converted into a timestamp type value by applying the format specified as the second argument.
• The format is the argument that specifies the pattern or format string (TEXT type) in the given input string timestamp.
• The TO_TIMESTAMP() function returns the timestamp type value with the timezone.

Read PostgreSQL WHERE with examples

Format

The timestamp value formatting patterns available in PostgreSQL, which can be used as the format string argument in the TO_TIMESTAMP() function:

format	Description
SS	It specifies the second (00-59)
MS	It specifies the millisecond (000-999)
US	It specifies the microsecond (000000-999999)
FF1	It specifies the tenth of a second (0-9)
FF2	It specifies the hundredth of a second (00-99)
FF3	It specifies the thousandth of a second that is a millisecond (000-999)
FF4	It specifies the tenth of a millisecond (0000-9999)
FF5	It specifies the hundredth of a millisecond (00000-99999)
FF6	It specifies the thousandth of a millisecond, which is a microsecond (000000-999999)
SSSS or SSSSS	It specifies the seconds past midnight (0-86399)
MI	It specifies the minute (00-59)
HH	It specifies the hour of a day (01-12)
HH12	It specifies the hour of a day (01-12)
HH24	It specifies the hour of a day (00-23)
AM or am or PM or pm	It specifies the meridian indicator without periods
A.M. or a.m. or P.M. or p.m.	It specifies the meridian indicator with periods
DAY	It specifies the name of the day where all characters are in uppercase with blank-padding to 9 characters.
Day	It specifies the name of the day, where all characters are capitalized with blank-padding to 9 characters
day	It specifies the name of the day where all characters are in lowercase with blanks for 9 characters.
DY	It specifies the abbreviated uppercase day name with three characters in English.
Dy	It specifies the abbreviated capitalized day name with three characters in English
dy	It specifies the abbreviated lowercase day name with three characters in English.
DDD	It specifies the day of the year (001-366)
IDDD	It specifies the day of the ISO 8601 week-numbering year (001-371, where day 1 of the year is Monday of the first ISO week)
DD	It specifies the day of the month (01-31)
D	It specifies the day of the week (1-7, that is Sunday to Saturday)
ID	It specifies the ISO 8601 day of the week (1-7, that is Monday to Sunday)
W	It specifies the week of the month (1-5, where the first week starts on the first day of the month)
WW	It specifies the week number of the year (1-53, where the first week starts on the first day of the year)
IW	It specifies the week number of the ISO 8601 week-numbering year (01-53, where the first Thursday of the year is in week 1)
MONTH	It specifies the month name where all the characters are in upper case, with blanks padded to 9 characters.
Month	It specifies the month name where all the characters are capitalized with blank-padded to 9 characters
month	It specifies the month name where all the characters are in lower case, with blank-padded to 9 characters.
MON	It specifies the abbreviated uppercase month name (3 chars in English, localized lengths vary)
Mon	It specifies the abbreviated capitalized month name with three characters in English
mon	It specifies the abbreviated lowercase month name with three characters in English.
MM	It specifies the month number (01-12)
RM	It specifies the month in upper case Roman numerals (I-XII, that is, January to December)
rm	It specifies the month in lowercase Roman numerals (i–xii, that is, January to December)
Q	It specifies the quarter of the year
Y,YYY	It specifies the four or more digits of the year with commas
YYYY	It specifies the four or more digits of the year without commas
YYY	It specifies the last three digits of the year
YY	It specifies the last two digits of the year
Y	It specifies the last digit of the year
IYYY	It specifies the ISO 8601 week-numbering year (4 or more digits)
IYY	It specifies the last three digits of the ISO 8601 week-numbering year
IY	It specifies the last two digits of the ISO 8601 week-numbering year
I	It specifies the last digit of the ISO 8601 week-numbering year
BC or bc or AD or ad	It specifies the era indicator without periods
B.C. or b.c. or A.D. or a.d.	It specifies the era indicator with periods
CC	It specifies the century in 2 digits (where the twenty-first century starts on 2001-01-01)
J	It specifies the Julian Date (integer days since November 24th, 4714 BC at local midnight)
TZM	It specifies the time-zone minutes
TZH	It specifies the time-zone hours
TM	It is applied to use localized day and month names (Translation Mode)
FM	It suppresses the blank padding and leading zeroes (Fill Mode)
FX	It specifies the fixed format global option(applies as a suffix in the format string)
SP	It enables formatting in spell mode (applies as a suffix in the format string)

Now, you have covered all the format string patterns available in PostgreSQL for the TO_TIMESTAMP() function. Let’s explore some examples to clarify the concepts.

The default format of the timestamp type value is shown in the example below:

SELECT TO_TIMESTAMP('2021-02-28 07:50:10', 'YYYY-MM-DD HH:MI:SS');

SELECT now()::timestamp;

SELECT current_timestamp;

SELECT now();

After executing the above query, I got the expected output as shown in the screenshot below.

In the 1st example from the above,

• YYYY corresponds to the specified Year 2021.
• MM corresponds to the specified Month 02.
• DD corresponds to the specified Date 28.
• HH corresponds to the specified Hour 07.
• MI corresponds to Minute 50.
• And SS corresponds to the specified Seconds 10.

NOTE:

• The TO_TIMESTAMP() function in PostgreSQL skips spaces in the input string, except when the FX (Fixed format) prefix is added to the format string argument. In this case, the spaces specified in the format string argument must match those in the input string; otherwise, the function returns an error.
• If a year is specified with fewer than four digits in the TO_TIMESTAMP() function, it is converted to the nearest year.
• You can also specify the part of the timestamp value to be returned. For example, if you specify the TIMESTAMP after the TO_TIMESTAMP() function, it returns the timestamp without a timezone. If you specify TIME, it returns only the time part of the timestamp value from the TO_TIMESTAMP function.

Now, let’s look at some examples to clarify the concepts.

Read How to Restart PostgreSQL (Linux, Windows, Mac)

Example 1: Converting to format DD/MM/YYYY

In PostgreSQL, we can convert a string having a date value in the format ‘DD/MM/YYYY’, into a timestamp type value by using the TO_TIMESTAMP() function and specifying the pattern in the format string argument.

SELECT '08/03/1998' AS input, TO_TIMESTAMP('08/03/1998', 'DD/MM/YYYY') AS output;

-- Passing invalid date value in the input string
SELECT '31/04/2020' AS input, TO_TIMESTAMP('31/04/2020', 'DD/MM/YYYY') AS output;

NOTE: The TO_TIMESTAMP() function checks the input string argument for any invalid Date/Time value; if there is any, then it returns an error.

Example 2: Format time part

You can convert a string having a date/time value into a timestamp type value output with a specified pattern for the time part as the format string argument in the TO_TIMESTAMP() function in PostgreSQL.

You can refer to Table 1 for specifying the format strings as per your needs.

SELECT TO_TIMESTAMP('2021-02-28 07:50:10', 'YYYY-MM-DD HH:MI:SS');

SELECT TO_TIMESTAMP('07:50:10 PM', 'HH12:MI:SS AM');

SELECT TO_TIMESTAMP('Mon, November 11:17:21:29', 'Dy, Month HH:MI:SS:MS');

-- To not get the default values for the Date and time zone
SELECT TO_TIMESTAMP('07:50:10 PM', 'HH12:MI:SS PM')::TIME;

After executing the above query, I got the expected output as shown in the screenshot below.

NOTE: When the TO_TIMESTAMP() function converts the input string into a timestamp, it converts the milliseconds and microseconds to seconds (after decimals).

Read How to connect to PostgreSQL database

Example 3: Converting to 24 hour

You can convert a string containing a date/time value into a timestamp type value in PostgreSQL by specifying the time format to be set to 24 hours, using HH24 in the format string argument of the TO_TIMESTAMP() function.

SELECT TO_TIMESTAMP('17:30:11', 'HH24:MI:SS');

SELECT TO_TIMESTAMP('07:50:10 PM', 'HH24:MI:SS AM');

After executing the above query, I got the expected output as shown in the screenshot below.

Example 4: Converting to milliseconds

You can convert a string having a date/time value into a timestamp type value output by specifying the millisecond as the MS in the format string argument of the TO_TIMESTAMP() function in PostgreSQL.

SELECT TO_TIMESTAMP('17:30:11:59', 'HH24:MI:SS:MS');

SELECT TO_TIMESTAMP('52', 'MS')::TIME;

I got the expected output after executing the above query as shown in the screenshot below.

Read PostgreSQL DROP COLUMN + 7 examples

Example 5: Converting to timezone

You can convert a string having a date/time value into a timestamp type value output by specifying the format for the time zone as the TZH/TZM in the format string argument of the TO_TIMESTAMP() function in PostgreSQL.

It does not support TZ for the timezone in the format string, which is only supported in the TO_CHAR() function.

SELECT TO_TIMESTAMP('2021-03-31 07:50:10-02:00', 'YYYY-MM-DD HH:MI:SSTZH:TZM');

SELECT TO_TIMESTAMP('07:50:10 PM +5', 'HH12:MI:SS AM TZH');

SELECT TO_TIMESTAMP('Mon, November 11:17:21:29 30', 'Dy, Month HH:MI:SS:MS TZM');

I got the expected output as shown in the screenshot below.

Example 6: Converting without timezone

You can convert a string having a date/time value into a timestamp type value without including the time zone by specifying the keywords WITHOUT TIME ZONE after the TO_TIMESTAMP() function in PostgreSQL.

SELECT TO_TIMESTAMP('11-07-2012 08:58:42 +7:30', 'MM-DD-YYYY HH24:MI:SS TZH:TZM')::TIMESTAMP WITHOUT TIME ZONE;

SELECT TO_TIMESTAMP('Mon, November 11:17:21:29', 'Dy, Month HH:MI:SS:MS')::TIMESTAMP WITHOUT TIME ZONE;

SELECT TO_TIMESTAMP('07:50:10:31-02:00', 'HH:MI:SS:MSTZH:TZM')::TIME;

I got the expected output as shown in the screenshot below.

Read PostgreSQL INSERT INTO table + 9 Examples

Example 7: Converting with timezone

You can convert a string having a date/time value into a timestamp type value output with the time zone by using the TO_TIMESTAMP() function in PostgreSQL with the specified pattern in the format string argument.

The return type of the TO_TIMESTAMP() function is TIMESTAMP WITH TIME ZONE. You can verify by using the pg_typeof() function.

SELECT TO_TIMESTAMP('2021-02-28 07:50:10+01:00', 'YYYY-MM-DD HH:MI:SSTZH:TZM');

SELECT pg_typeof(TO_TIMESTAMP('2021-02-28 07:50:10+01:00', 'YYYY-MM-DD HH:MI:SSTZH:TZM'));

SELECT TO_TIMESTAMP('2021-02-28 07:50:10', 'YYYY-MM-DD HH:MI:SS')::TIMESTAMP WITH TIME ZONE;

SELECT TO_TIMESTAMP('07:50:10:31', 'HH:MI:SS:MS');

I got the expected output after executing the above query.

Example 8: Converting with timezone UTC

You can convert a string having a date/time value into a timestamp type value output with the time zone ‘UTC’ by using the TO_TIMESTAMP() function in PostgreSQL, with the specified pattern in the format string argument, followed by the keywords AT TIME ZONE ‘UTC’.

You can give any timezone abbreviation available in PostgreSQL in the single quotes in the place of UTC.

SELECT TO_TIMESTAMP('2021/12/30 14:52:49', 'YYYY/MM/DD HH24:MI:SS') AT TIME ZONE 'UTC';

SELECT TO_TIMESTAMP('11-07-2012 08:58:42 +7:30', 'MM-DD-YYYY HH24:MI:SS TZH:TZM') AT TIME ZONE 'UTC';

SELECT TO_TIMESTAMP('07:50:10:31', 'HH:MI:SS:MS') AT TIME ZONE 'UTC';

After executing the above query, I got the expected output as shown in the screenshot below.

Read PostgreSQL ALTER TABLE + 19 Examples

Example 9: Converting timezone with offset

Time Zone offset is the amount of time that is subtracted from or added to the UTC (Coordinated Universal Time) time to obtain the local solar time.

You can convert a string having a date/time value into a timestamp type value output with the time zone offset in the input string by using the TO_TIMESTAMP() function in PostgreSQL and including the TZH and TZM in the format string argument at the specified position to indicate the Offset.

The TO_TIMESTAMP() function in PostgreSQL does not support OF in the format string to specify the timezone offset, which is supported only in the TO_CHAR() function.

SELECT TO_TIMESTAMP('2021/12/30 14:52:49 +3:30', 'YYYY/MM/DD HH24:MI:SS TZH:TZM');

SELECT TO_TIMESTAMP('11-07-2012 08:58:42 +7:30', 'MM-DD-YYYY HH24:MI:SS TZH:TZM');

SELECT TO_TIMESTAMP('07:50:10:31 -01:00', 'HH:MI:SS:MS TZH:TZM');

I got the expected output after executing the above query as shown in the screenshot below.

Example 10: Converting epoch value

The epoch in PostgreSQL is the number of seconds since 1970-01-01 00:00:00+00.

You can convert an epoch value into the TIMESTAMP value in PostgreSQL by using the TO_TIMESTAMP() function with only one argument of double-precision type, that is, the epoch value specified.

The syntax is as follows:

TO_TIMESTAMP(epoch_value)

In the above syntax, there is only one argument, epoch_value, which is a double-precision value representing the epoch that needs to be converted into a timestamp.

SELECT TO_TIMESTAMP(1658772421) AS from_epoch;

SELECT TO_TIMESTAMP(1657742429.31675) AS epoch_fractional_second;

I got the expected output after executing the above query as shown below.

Read PostgreSQL DATE Functions with Examples

Example 11: Converting to iso 8601 value

You can convert a string having a date/time value into an iso 8601 timestamp type value output with the time zone by using the I/IY/IYY/IYYY pattern for the year in the format string argument of the TO_TIMESTAMP() function.

SELECT TO_TIMESTAMP('1', 'IY');

SELECT TO_TIMESTAMP('21', 'IY');

SELECT TO_TIMESTAMP('21', 'IYY');

SELECT TO_TIMESTAMP('121', 'IYY');

SELECT TO_TIMESTAMP('1', 'IYYY');

SELECT TO_TIMESTAMP('11', 'IYYY');

SELECT TO_TIMESTAMP('211', 'IYYY');

SELECT TO_TIMESTAMP('2011', 'IYYY');

After executing the above query, I got the expected output as shown in the screenshot below.

Performance Considerations

1. Preprocess in Batches: For large imports, convert and update in manageable batches rather than row-by-row.
2. Create Functional Indexes: If you frequently query based on converted timestamps, consider creating a functional index using the query below.

CREATE INDEX idx_timestamp_conv ON your_table (TO_TIMESTAMP(date_string, 'MM/DD/YYYY'));

3. Store Converted Values: If you repeatedly convert the same string values, consider storing the converted timestamp in a dedicated column to avoid redundant processing.

Conclusion

The TO_TIMESTAMP Function is a powerful tool in PostgreSQL that allows you to control date and time conversions. By understanding its format patterns and capabilities, you can easily handle any date/time string format you encounter in your applications.

In this PostgreSQL tutorial, we have learned about the PostgreSQL TO_TIMESTAMP() function, which converts a string into a timestamp type value based on a specified format, with multiple examples.

You may like the following PostgreSQL tutorials:

• PostgreSQL ADD COLUMN + 17 Examples
• PostgreSQL vs SQL Server: Detailed Comparison
• PostgreSQL DATE Format
• PostgreSQL WHERE IN with examples
• PostgreSQL CASE with Examples
• Postgresql date_trunc function