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

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

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.

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

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…

i’m facing the same problem…