Postgresql – Update record with converted epoch timestamp from another column

datetimefunctionspostgresqltimestamptimezone

I'm trying to convert epoch data in one column (timestamp_epoch) to a legitimate timestamp in another column (timestamp) for every record in a table. In reading the PostgreSQL Documentation it appears I can do this using the to_timestamp() function, but I get an error indicating this function does not exist.

UPDATE table
SET timestamp = to_timestamp(timestamp_epoch) AT TIME ZONE 'EST'
WHERE timestamp IS NULL;

Server is a PostgreSQL 12.1 and I'm using pgAdmin4 to run the query. When the query runs, I get an error indicating the function doesn't exist and that I might need to add explicit type casts… for which I'm lost on how to properly do if that is indeed needed.

ERROR:  function to_timestamp(text) does not exist
LINE 2: SET timestamp = to_timestamp(timestamp_epoch)
                        ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.
SQL state: 42883
Character: 56

Any help on converting the epoch data in one column to a legitimate timestamp in another column is appreciated.

Thanks!

Best Answer

Your timestamp_epoch is defined with the data type text (which is what "function to_timestamp(text) does not exist" is telling you).

You need to first convert your text value to a bigint:

to_timestamp(timestamp_epoch::bigint) AT TIME ZONE 'EST'
Related Question