1

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:

enter image description here

Type of my timestampz column:

enter image description here

Jakub Szlaur
  • 1,852
  • 10
  • 39
  • If your column `timestamp` is a unix epoch, you need to convert **that** using `to_timestamp()`: https://stackoverflow.com/questions/42219890/how-to-convert-unix-epoch-to-a-timestamp –  Feb 14 '22 at 15:25
  • I have edited the question with your suggestion. – Jakub Szlaur Feb 14 '22 at 15:33
  • 2
    Is your `timestamp` column a `varchar`? If it's not (but a proper `timestamp` data type), then all you need is `"timestamp" >= TO_TIMESTAMP(1644770125499 / 1000)` –  Feb 14 '22 at 15:34
  • I don't know - editor doesn't say if its a varchar (look at picture in the question). – Jakub Szlaur Feb 14 '22 at 15:35
  • Well, apparently it **is** a `timestamptz` so there is no need to convert the timestamp from `to_timestamp()` into a text value. –  Feb 14 '22 at 15:36

1 Answers1

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)
Laurenz Albe
  • 209,280
  • 17
  • 206
  • 263