Convert epoch time to timestamp in Postgresql

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:

SQL Error [42883]: ERROR: operator does not exist: character varying * interval
  Hint: No operator matches the given name and argument types. You might need to add explicit type casts.
  Position: 64

SQL Error [42883]: ERROR: operator does not exist: character varying / integer
  Hint: No operator matches the given name and argument types. You might need to add explicit type casts.
  Position: 33

How to find the solution? What we need apparently just combining Timestamp with epoch, cast field to int to be save and multiple it with INTERVAL.

Here is the query:

SELECT TIMESTAMP 'epoch' + (<table>.field::int) * INTERVAL '1 second' as started_on from <table>;

To make group by by weekly from epoch time

SELECT COUNT(*), another_column, date_trunc('week', TIMESTAMP 'epoch' + (epoch_column::int) * INTERVAL '1 second') AS weekly from builds group by weekly, another_column order by weekly desc;

Hope this can help you.