Convert UNIX date time to datetime


I am trying to create a date query variable from a table.

The data column in the table is stored as the DateTime type, but when I run the query in the variable page it returns the Unix time stamp format.
The same query in the psql returns the result as expected

So I tried the following queries and none of them is working.

select TO_TIMESTAMP(datetime) from model_view
select timezone('AEDT', to_timestamp(datetime)) from model_view
select extract(epoch from datetime) from model_view
select to_timestamp(extract(epoch from datetime)) from model_view

Also, if I use the grafana macros as follows it returns a single value

select $__timeFrom(datetime) from model_view


any recommendation?


1 Like

welcome to the :grafana: community @km1729!

This sounds like this bug. There is a temporary workaround mentioned in the comments that has worked for some users:

The suggested short-term workaround would be for you to convert your time column into a string using something like SELECT to_char(<time column>, 'YYYY-MM-DD"T"HH24:MI:SS.US"Z"');

I am using Postgres too and met same issue. Thanks @melori.arellano ! :smiley:

Here is my query:

set time zone 'america/los_angeles'
  id as __value,
  to_char(start_time, 'yyyy-mm-dd hh24:mi:ss') as __text
order by start_time desc

(Without set time zone 'america/los_angeles', it will show UTC time instead.)