How to convert BIGINT to TIMESTAMPZ in a query?

Question

Using:

• PostgreSQL 14 in Supabase
• Grafana cloud

I am trying to convert BIGINT timestamp in milliseconds to TIMESTAMPZ in PostgreSQL 14.

The BIGINT is a constant stored in a $__to and $__from. I am trying to query data in a certain time range with this query:

SELECT
  "timestamp" AS "time",
  etalon,
  humidity,
  temperature
FROM "values"
WHERE
  timestamp >= TO_TIMESTAMP($__from, 'DD/MM/YYYY HH24:MI:SS')
  and timestamp <  TO_TIMESTAMP($__to, 'DD/MM/YYYY HH24:MI:SS')

The query above result in this error:

function to_timestamp(bigint, unknown) does not exist

I have looked into these topics but couldn't find a solution that would work:

1. Postgres timestamp to unix time in milliseconds as a bigint
2. https://dba.stackexchange.com/questions/215354/convert-date-format-into-bigint-format-in-postgresql
3. How to format bigint field into a date in Postgresql?

Edit

Using Quassnoi solution doesn't work either:

SELECT
  "timestamp" AS "time",
  etalon,
  humidity,
  temperature
FROM "values"
WHERE
  timestamp >= TO_CHAR(TO_TIMESTAMP(1644770125499 / 1000), 'DD/MM/YYYY HH24:MI:SS')
  and timestamp <  TO_CHAR(TO_TIMESTAMP(1644770125499 / 1000), 'DD/MM/YYYY HH24:MI:SS')

Results in:

operator does not exist: timestamp with time zone >= text

Using suggestion from comments I do convert the BIGINT but I get weird looking timestamp:

Type of my timestampz column:

Answer 1

You have to use the single-argument form of to_timestamp:

SELECT to_timestamp(1644853209.6);

       to_timestamp       
══════════════════════════
 2022-02-14 16:40:09.6+01
(1 row)