How to_timestamp() Works in PostgreSQL

According to the PostgreSQL documentation, there are two functions called to_timestamp():

One converts the Unix epoch to a timestamp. The Unix epoch is the number of seconds since 1970-01-01 00:00:00+00.
The other converts a string to a timestamp.

More specifically, each function converts the value to a timestamp with time zone value.

Although the Postgres documentation presents them as two separate to_timestamp() functions, I present them as if they’re one function that accepts either one argument, or two.

Syntax

You can use to_timestamp() in the following ways:

to_timestamp(double precision)
to_timestamp(text, text)

Using the first syntax, the argument is provided as a double precision value, and it is the Epoch value, in double precision you want converted to a timestamp.

Using the second syntax, the first argument is the date, and the second argument is the format string.

Convert the Unix Epoch

Here’s a basic example to demonstrate how to convert the Epoch to a timestamp.

SELECT to_timestamp(1658792421);

Result:

2022-07-26 09:40:21+10

In my case the timezone offset is +10 and so that is what’s returned.

Fractional Seconds

Here’s an example with fractional seconds.

SELECT to_timestamp(1658792421.123456);

Result:

2022-07-26 09:40:21.123456+10

Formatting

Here’s an example of using the second syntax to format a date value.

SELECT to_timestamp('21 Oct 2022', 'DD Mon YYYY');

Result:

2022-10-21 00:00:00+10

The second argument is provided to indicate how the first argument is formatted.

Here’s another format in order to further illustrate this point.

SELECT to_timestamp('21/10/2022', 'DD/MM/YYYY');

Result:

2022-10-21 00:00:00+10

If the format of the second argument doesn’t match the format of the first argument, you may end up with an error.

For example:

SELECT to_timestamp('21 October 2022', 'DD Mon YYYY');

Result:

ERROR: invalid value "ober" for "YYYY"
DETAIL: Value must be an integer.

The format string (second argument) can be any template pattern with an optional modifier.

Here’s a full list of template patterns and modifiers that you can use with this function.

The Return Type

As mentioned, the return type is timestamp with time zone. We can verify this with the pg_typeof() function.

SELECT pg_typeof(to_timestamp(1658792421.123456));

Result:

timestamp with time zone

Extracting Date Parts

You can use various methods to extract date parts from the timestamp value.

For example, you can use the extract() function.

SELECT extract('month' from to_timestamp(1658792421));

Result:

And you can use the to_char() function if you need to do something like, display the month name instead of the month number.

SELECT to_char(to_timestamp(1658792421), 'Month');

Result:

July

And here it is using the formatting syntax.

SELECT to_char(to_timestamp('1st Oct 2022', 'FMDDth Mon YYYY'), 'Month');

Result:

October

If you only have the month number, you can use the following example to convert the month number to the month name.

SELECT to_char(to_timestamp(3::text, 'MM'), 'Month');

Result:

March

And vice-versa (convert month name to month number).

In the following example I extract the month number by using date_part() as an alternative to extract().

SELECT date_part('month', (to_timestamp('Mar 2022', 'Mon')));

Result: