TO_TIMESTAMP
MySQL equivalent:
Converts Unix time or a string with a format template to a timestamp.
PostgreSQL 17.5
TO_TIMESTAMP(unix_timestamp)
TO_TIMESTAMP(string, format)Parameters
- unix_timestampdouble precision
- Unix time, seconds since 1970-01-01 00:00:00 UTC, can be fractional
- stringtext
- String with a date and time
- formattext
- Format template, for example DD.MM.YYYY HH24:MI
Return value
A timestamp with time zone (timestamptz); fractional seconds are kept
Examples
Timestamp from Unix time
PostgreSQL 17.5
SELECT TO_TIMESTAMP(1717584000);Timestamp from a string
PostgreSQL 17.5
SELECT TO_TIMESTAMP('08.03.2024 14:30', 'DD.MM.YYYY HH24:MI');Details
The string form parses it as loosely as TO_DATE: extra text after the template is skipped, and there is an error only if a field cannot be read or the date does not exist. The result is shown in the time zone from the TimeZone setting, which is UTC in the sandbox.