Show time variable with local timezone

Grafana PostgreSQL
1
  • What Grafana version and what operating system are you using? Grafana v11.3.0 (d9455ff7db) on Debian GNU/Linux 12 (bookworm), PostgreSQL 15.8 (Debian 15.8-0+deb12u1)

Hello everyone. I try to create a dashboard where the user can select a date from the database to retrieve information. For that, I have added a variable with the following sql request:

SELECT match_time_date FROM matchs;

The problem is: by using this synthax, I got timestamp and not a date:

image

So I tried to convert to a char the date:

SELECT to_char(match_time_date, 'DD-MM-YYYY HH24:MI') FROM matchs;

But the result is always in UTC, and not on the timezone of the browser or the one I set up in Grafana:

image

On the database side, this field is a timestamp with timezone (my database is on Europe/Paris time):

SELECT match_time_date FROM matchs;
postgres=# select match_time_date from matchs;
    match_time_date
------------------------
 2024-10-13 20:00:00+02
 2024-10-20 21:30:00+02
 2024-10-27 20:00:00+01
 2024-11-03 20:00:00+01
 2024-11-17 20:00:00+01
 2024-11-10 09:00:00+01
 2024-12-01 20:00:00+01
 2024-11-24 20:30:00+01
(8 rows)
postgres=# \d+ matchs;
                                                       Table "matchs"
      Column       |           Type           | Collation | Nullable | Default | Storage  | Compression | Stats target | Description
-------------------+--------------------------+-----------+----------+---------+----------+-------------+--------------+-------------
 match_time_date   | timestamp with time zone |           | not null |         | plain    |             |              |

I didn’t find any solution yet. I would like to print the date on the local time. For example, if I’m in Paris, for the first one I want to see:
2024-10-13 20:00:00

And for London:
2024-10-13 18:00:00

Do you know if it’s possible to achieve that, and if yes, how ?

Thank you for your help.

2

There should be $__timezone variable. Use it in your query and add date add logic there based on that variable value. Use browser console to see queries and debug your work.

Keep in mind that panel queries must be in the utc, otherwise good luck with billions and billions timezone related issues.

3

Thank you for the hint, it works.

For people searching:

SELECT to_char(match_time_date::timestamptz at time zone '$__timezone', 'DD-MM-YYYY HH24:MI') FROM matchs ORDER BY match_time_date ASC;

And if I need this variable to be used in another request:

WHERE match_time_date = to_timestamp('$match_date', 'DD-MM-YYYY HH24:MI')::timestamp AT time zone '$__timezone';

About UTC, data are stored inside my database in timestamp with timezone. I don’t know if Grafana get them in UTC, but I tested in Local time (GMT +2), GMT and GMT+3 and it works.

Thank you for your help.