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:

formatDescription
SSIt specifies the second (00-59)
MSIt specifies the millisecond (000-999)
USIt specifies the microsecond (000000-999999)
FF1It specifies the tenth of a second (0-9)
FF2It specifies the hundredth of a second (00-99)
FF3It specifies the thousandth of a second that is a millisecond (000-999)
FF4It specifies the tenth of a millisecond (0000-9999)
FF5It specifies the hundredth of a millisecond (00000-99999)
FF6It specifies the thousandth of a millisecond that is a microsecond (000000-999999)
SSSS or SSSSSIt specifies the seconds past midnight (0-86399)
MIIt specifies the minute (00-59)
HHIt specifies the hour of a day (01-12)
HH12It specifies the hour of a day (01-12)
HH24It specifies the hour of a day (00-23)
AM or am or PM or pmIt specifies the meridiem indicator without periods
A.M. or a.m. or P.M. or p.m.It specifies the meridiem indicator with periods
DAYIt specifies the name of the day where all characters are in upper case with blank-padding to 9 characters
DayIt specifies the name of the day where all characters are capitalized with blank-padding to 9 characters
dayIt specifies the name of the day where all characters are in lower case with blank-padding to 9 characters
DYIt specifies the abbreviated upper case day name with 3 characters in English
DyIt specifies the abbreviated capitalized day name with 3 characters in English
dyIt specifies the abbreviated lower case day name with 3 characters in English
DDDIt specifies the day of the year (001-366)
IDDDIt 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)
DDIt specifies the day of the month (01-31)
DIt specifies the day of the week (1-7, that is Sunday to Saturday)
IDIt specifies the ISO 8601 day of the week (1-7, that is Monday to Sunday)
WIt specifies the week of the month (1-5, where the first week starts on the first day of the month)
WWIt specifies the week number of the year (1-53, where the first week starts on the first day of the year)
IWIt specifies the week number of ISO 8601 week-numbering year (01-53, where the first Thursday of the year is in week 1)
MONTHIt specifies the month name where all the characters are in upper case with blank-padded to 9 characters
MonthIt specifies the month name where all the characters are capitalized with blank-padded to 9 characters
monthIt specifies the month name where all the characters are in lower case with blank-padded to 9 characters
MONIt specifies the abbreviated upper case month name (3 chars in English, localized lengths vary)
MonIt specifies the abbreviated capitalized month name with 3 characters in English
monIt specifies the abbreviated lower case month name with 3 characters in English
MMIt specifies the month number (01-12)
RMIt specifies the month in upper case Roman numerals (I-XII, that is January to December)
rmIt specifies the month in lower case Roman numerals (i–xii, that is January to December)
QIt specifies the quarter of the year
Y,YYYIt specifies the 4 or more digits of the year with commas
YYYYIt specifies the 4 or more digits of the year without commas
YYYIt specifies the last 3 digits of the year
YYIt specifies the last 2 digits of the year
YIt specifies the last digit of the year
IYYYIt specifies the ISO 8601 week-numbering year (4 or more digits)
IYYIt specifies the last 3 digits of ISO 8601 week-numbering year
IYIt specifies the last 2 digits of ISO 8601 week-numbering year
IIt specifies the last digit of ISO 8601 week-numbering year
BC or bc or AD or adIt specifies the era indicator without periods
B.C. or b.c. or A.D. or a.d.It specifies the era indicator with periods
CCIt specifies the century in 2 digits (where the twenty-first century starts on 2001-01-01)
JIt specifies the Julian Date (integer days since from November 24th, 4714 BC at local midnight)
TZMIt specifies the time-zone minutes
TZHIt specifies the time-zone hours
TMIt is applied to use localized day and month names (Translation Mode)
FMIt suppresses the blank padding and leading zeroes (Fill Mode)
FXIt specifies the fixed format global option(applies as a suffix in the format string)
SPIt enables formatting in spell mode (applies as a suffix in the format string)
Table 1: Timestamp format string patterns available in PostgreSQL

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

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

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

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

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

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

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
PostgreSQL TO_TIMESTAMP with timezone

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';
PostgreSQL TO_TIMESTAMP with timezone utc
PostgreSQL TO_TIMESTAMP with timezone 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 timezone offset
PostgreSQL TO_TIMESTAMP timezone offset

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

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

You may like the following PostgreSQL tutorials:

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

Leave a Comment