1

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!

0

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'
1
  • The text data type explanation makes sense now. It did work, and thank you! – jbush Feb 19 '20 at 1:35

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.