PostgreSQL: Get date and time from unix string format field-CodePudding

I have such a query:

select
(featuremap ->> 'column.time:value')
from db.table;

This returns the unix timestamp as a string value. I tried several things: E.g.:

select
extract(epoch from(featuremap ->> 'column.time:value'))
from db.table;

Which returns this error: ERROR: function pg_catalog.date_part(unknown, text) does not exist

And:

select
to_timestamp(featuremap ->> 'column.time:value')
from db.table;

which returns this error: ERROR: function to_timestamp(text) does not exist

What do I have to do in this case in order to get the date and time from the unix timestamp string/text field?

CodePudding user response：

Use to_timestamp with a single epoch argument. Since the original type of the argument is text it needs to be first cast to a numeric type.

select
to_timestamp((featuremap ->> 'column.time:value')::double precision)
from db.table;