PostgreSQL TO_TIMESTAMP function + Examples

In this PostgreSQL tutorial, we will discuss the PostgreSQL TO_TIMESTAMP() function to convert a string into timestamp type value based on some specified format, and will also cover the following topics:

• PostgreSQL TO_TIMESTAMP function
• PostgreSQL TO_TIMESTAMP format
• PostgreSQL TO_TIMESTAMP format DD/MM/YYYY
• PostgreSQL TO_TIMESTAMP format time
• PostgreSQL TO_TIMESTAMP 24 hour
• PostgreSQL TO_TIMESTAMP format milliseconds
• PostgreSQL TO_TIMESTAMP format timezone
• PostgreSQL TO_TIMESTAMP without timezone
• PostgreSQL TO_TIMESTAMP with timezone
• PostgreSQL TO_TIMESTAMP with timezone utc
• PostgreSQL TO_TIMESTAMP timezone offset
• PostgreSQL TO_TIMESTAMP epoch
• PostgreSQL TO_TIMESTAMP iso 8601

PostgreSQL TO_TIMESTAMP function

PostgreSQL serves some data type formatting functions which are used as a very powerful set of tools to convert various data type values (like date-time/timestamp, integer, numeric, 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 given specified formatting. The syntax is as follows:

TO_TIMESTAMP(timestamp, format)

In the above syntax,

• The timestamp is the argument that represents the string (TEXT type) with date/time value that is to be converted into the timestamp type value by applying the format, specified as the second argument.
• The format is the argument that specifies the pattern/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

PostgreSQL TO_TIMESTAMP format

The timestamp value formatting patterns available in PostgreSQL, that 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 that 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 meridiem indicator without periods
A.M. or a.m. or P.M. or p.m.	It specifies the meridiem indicator with periods
DAY	It specifies the name of the day where all characters are in upper case 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 lower case with blank-padding to 9 characters
DY	It specifies the abbreviated upper case day name with 3 characters in English
Dy	It specifies the abbreviated capitalized day name with 3 characters in English
dy	It specifies the abbreviated lower case day name with 3 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 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 blank-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 upper case month name (3 chars in English, localized lengths vary)
Mon	It specifies the abbreviated capitalized month name with 3 characters in English
mon	It specifies the abbreviated lower case month name with 3 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 lower case Roman numerals (i–xii, that is January to December)
Q	It specifies the quarter of the year
Y,YYY	It specifies the 4 or more digits of the year with commas
YYYY	It specifies the 4 or more digits of the year without commas
YYY	It specifies the last 3 digits of the year
YY	It specifies the last 2 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 3 digits of ISO 8601 week-numbering year
IY	It specifies the last 2 digits of ISO 8601 week-numbering year
I	It specifies the last digit of 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 from 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 TO_TIMESTAMP() function. So, let’s do some examples to make the concepts clear.

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();

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 the specified Minute 50.
• And SS corresponds to the specified Seconds 10.

NOTE:

• The TO_TIMESTAMP() function in PostgreSQL skips the spaces in the input string, except when the FX (Fixed format) prefix is added in the format string argument then the spaces specified in the format string argument must be same as the input string, else the function returns an error.
• If year is specified in less than 4 digits, in the TO_TIMESTAMP() function then, it converts it to the nearest year.
• You can also specify the part of the timestamp value to be returned. eg., if you specify the ::TIMESTAMP, after the TO_TIMESTAMP() function, then it retirns the timestamp without timezone, if you specify ::TIME, then it returns only the time part of from the timestamp value of TO_TIMESTAMP function.

Now, let’s do some examples to make the concepts clear.

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

PostgreSQL TO_TIMESTAMP 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.

PostgreSQL TO_TIMESTAMP format time

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 need.

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;

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

Read How to connect to PostgreSQL database

PostgreSQL TO_TIMESTAMP 24 hour

You can convert a string having a date/time value into a timestamp type value in PostgreSQL, by specifying the time format to be set to 24 hours as the 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');

This is an example of PostgreSQL TO_TIMESTAMP 24 hour.

PostgreSQL TO_TIMESTAMP format 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;

The above is an example of PostgreSQL TO_TIMESTAMP format milliseconds.

Read PostgreSQL DROP COLUMN + 7 examples

PostgreSQL TO_TIMESTAMP format 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 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');

PostgreSQL TO_TIMESTAMP 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;

This is how to convert a string to a timestamp type without timezone using PostgreSQL TO_TIMESTAMP.

Read PostgreSQL INSERT INTO table + 9 Examples

PostgreSQL TO_TIMESTAMP 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');

PostgreSQL TO_TIMESTAMP 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 the 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';

Read PostgreSQL ALTER TABLE + 19 Examples

PostgreSQL TO_TIMESTAMP timezone offset

Time Zone offset is the amount of time that is when 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.

TO_TIMESTAMP() function in PostgreSQL does not support OF in the format string to specify the timezone offset, which is supported only in 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');

PostgreSQL TO_TIMESTAMP epoch

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, the epoch_value, which is the double-precision value that represents the epoch that has to be converted into the timestamp.

SELECT TO_TIMESTAMP(1658772421) AS from_epoch;

SELECT TO_TIMESTAMP(1657742429.31675) AS epoch_fractional_second;

Read PostgreSQL DATE Functions with Examples

PostgreSQL TO_TIMESTAMP iso 8601

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');

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

In this PostgreSQL tutorial, we have learned about PostgreSQL TO_TIMESTAMP() function to convert a string into timestamp type value based on some specified format, and have also covered the following topics:

• PostgreSQL TO_TIMESTAMP function
• PostgreSQL TO_TIMESTAMP format
• PostgreSQL TO_TIMESTAMP format DD/MM/YYYY
• PostgreSQL TO_TIMESTAMP format time
• PostgreSQL TO_TIMESTAMP 24 hour
• PostgreSQL TO_TIMESTAMP format milliseconds
• PostgreSQL TO_TIMESTAMP format timezone
• PostgreSQL TO_TIMESTAMP without timezone
• PostgreSQL TO_TIMESTAMP with timezone
• PostgreSQL TO_TIMESTAMP with timezone utc
• PostgreSQL TO_TIMESTAMP timezone offset
• PostgreSQL TO_TIMESTAMP epoch
• PostgreSQL TO_TIMESTAMP iso 8601