June 23, 2022, 8:22am
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
welcome to the
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"');
06:40PM - 02 Jul 21 UTC
Please use this template to create your bug report. By providing as much i
… nfo as possible you help us understand the issue, reproduce it and resolve it for you quicker. Therefor take a couple of extra minutes to make sure you have provided all info needed.
PROTIP: record your screen and attach it as a gif to showcase the issue.
- Questions should be posted to: https://community.grafana.com
- Use query inspector to troubleshoot issues: https://bit.ly/2XNF6YS
- How to record and attach gif: https://bit.ly/2Mi8T6K
When creating a variable from a SQL source (I tested with MySQL and PostgreSQL datasources) with TIMESTAMP(TZ) column type, the variable is the timestamp as integer unlike before where it was an RFC3339 representation.
**What you expected to happen**:
The variable to be formatted in RFC3339 so that it's easy to use it in other queries.
**How to reproduce it (as minimally and precisely as possible)**:
1. Have a PostgreSQL/MySQL datasource with a table which has a column of type 'TIMESTAMP' or 'TIMESTAMPTZ'
2. Create a variable that uses this column (i.e. `SELECT ts FROM data WHERE id=1 LIMIT 1`)
3. Observe that the displayed variable is a Unix timestamp instead of formatted one (i.e. 1625250397000 vs 2021-07-02T22:27:25Z)
Here are some pictures that show the issue (Observe the "Preview of values" section):
## Grafana 7
## Grafana 8
**Anything else we need to know?**:
[I've created a PR that demonstrates a potential fix for the issue](https://github.com/grafana/grafana/pull/36405), but I'm not sure if the Grafana team wants to handle this from the backend, or the frontend side. I've noticed significant changes on how the data is provided from the backend to the frontend which made me doubt if this needs to be fixed on the backend as frontend now has information about the type of the variable:
<summary>Grafana 7 response structure example</summary>
"executedQueryString":"SELECT end_datetime FROM times limit 1;",
<summary>Grafana 8 response structure example</summary>
"executedQueryString":"select ts from data limit 1;"
Additionally, I believe these issues are relevant:
- Broken variables from SQL sources: https://github.com/grafana/grafana/issues/35391
- PR that brings the "frame" concept: https://github.com/grafana/grafana/pull/32257
- Grafana version: 8.0.3
- Data source type & version: PostgreSQL 13
- OS Grafana is installed on: Linux
- User OS & Browser: Arch Linux, Firefox 87
- Grafana plugins: x
- Others: x