Grafana/Postgresql: how to convert date stored in UTC to local timezone

1

I have a date stored in postgres db, e.g. 2019-09-03 15:30:03 . Timezone of postgres is UTC.

When Grafana gets the date, it is 2020-09-03T15:30:03.000000Z . If I now run date_trunc(‘day’, 2020-09-03T15:30:03.000000Z ), I get 2020-09-03T00:00:00.000000Z . But, I want midnight in my local timezone.

  1. How do I get the local timezone (offset) in postgres or grafana?
  2. Could I get the timezone in military style, instead of “Z” for UTC “B”?
  3. Or can I somehow subtract the offset of the local timezone to get a UTC date corresponding to midnight local time?

Thanks in advance
Michael

1 Like
2

Try using the function “timezone(zone, timestamp)” or the construct “timestamp
AT TIME ZONE zone” in your SQL query.

See section 9.9.3 of
https://www.postgresql.org/docs/9.2/functions-datetime.html

Antony.

3

Yes, that would do it, if I have the local (browser’s?) timezone.
Cannot find, how to get that…

4

I need to get the dashboard timezone to use it in the SQL query to set the timezone for the postgresql session. I searched for it, but could not find anything.

That is such a basic thing, it must be possible somehow…

5

i’m facing the same problem…

6

This worked for me: time zone as 'UCT’
I use postgresql and Grafana 7.1 on SLES
For me the following modification worked:
Before:
select time as “My Time”,
After:
select time AT TIME ZONE ‘CEST’ as “My Time”,

Where time column is of type “timestamp without time zone”
Clearly, this works well for CEST time zone or if you have any fixed one.
If you have users in multiple time zones, I think you shall set the time zone of your server.

Another option, not the nicest way: use a variable to set it.

7

Actually, I have kind of worked around it by setting not the server timezone, but the (Grafana) client time zone with the environment variable PGTZ. And then using select current_setting('TIMEZONE')

Still surprising, that you cannot ask Grafana “in what timezone are you displaying the data” in a query…

3 Likes
8

would be thanksfull if could provide more details how u did this workaround? new with grafana and i have issue presenting events in localtime ,thanks

1 Like
9

Regarding the work around:
Well, it is juet a change in the postgres query change.

Do you get results in the wrong time zone with postgres?
If so, just change the request (see before, after in my previous comment)
If not, this solution is not yours.