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

user_3864922 0
Viewed 2301

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

grafana timezone postgresql
1 Answers

Get the local timezone (offset):

select to_char(now(), 'OF');
-- result '+03' for EEST

Get UTC time corresponding to midnight local time:

select date_trunc('DAY', now()) at time zone 'UTC';
-- result '2020-06-05 21:00:00.0' for 13:30 EEST on 2020-06-06

Convert UTC time to local timezone time:

select now();
-- Local time is 2020-06-06 13:43:27.482463
select (now() at time zone 'UTC');
-- UTC time is 2020-06-06 10:43:27.482463

select '2020-06-06 10:43:27.482463UTC'::timestamp with time zone; 
-- UTC time converted to local time is 2020-06-06 13:43:27.482463

Hope that this helps.

user_23020320
Related