PostgreSQL: Get date and time from unix string format field

Question

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?

Stefanov.sm · Accepted Answer · 2022-11-14 13:39:30Z

1

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;

answered Nov 14, 2022 at 13:39

Stefanov.sm

13.2k2 gold badges24 silver badges24 bronze badges

Sign up to request clarification or add additional context in comments.

1 Answer 1