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.
Thanks in advance Michael
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.