Epoch time stored with bigint format in PostgreSQL. Most of problem when converting this epoch format to date or timestamp is because we deal with time being stored in number format and integer column type.
When I’m trying to convert epoch time that stored in bigint format, I found several way ready on Stackoverflow but didn’t works. Several script like below is not working for the latest PostgreSQL 13 version.
select *, to_timestamp(time in milli sec / 1000) from mytable SELECT TIMESTAMP WITH TIME ZONE 'epoch' + 982384720 * INTERVAL '1 second'; SELECT DATE(builds.build_created/ 1000) FROM builds;
You may trying with several approach like to_timestamp, to_date and receive several error results like :
1. Timezone not found
2. Need to cast
3. Or Operator does not exists
Some errors details eg: