I have a table with a column defined as:
"timestamp" int8 NULL,
which stores values like '1638462043745210034' When I try to cast it to timestamp or timestamp with time-zone
SELECT '1638462043745210034'::timestamp at time zone 'UTC' ;
it returns an error:
date/time field value out of range: "1638462043745210034" Hint: Perhaps you need a different "datestyle" setting.
What kind of datestyle can it be and how can it be converted to a normal timestamp?
CodePudding user response:
Looks like nanoseconds since the epoch:
SELECT to_timestamp(1638462043745210034 / 1000000000.0);
to_timestamp
══════════════════════════════
2021-12-02 17:20:43.74521 01
(1 row)