How to group time with custom timezone other than UTC?

1

I am using postgresql as datasource and there are some metrics I need to compute over a 24h window. For example:

SELECT
  $__timeGroup(created_on,24h),
  count(_id) AS "number of reviews"
FROM product_reviews
WHERE
  $__timeFilter(created_on)
GROUP BY 1
ORDER BY 1

The created_on is a timestamp field in UTC timezone. As I noticed the time window used for grouping in this example is always UTC time from midnight to midnight. For example a bar displayed as for “2018-10-21:00:00:00” is using time bucket 2018-10-20:00:00:00 UTC to 2018-10-20:00:00:00 UTC for data aggregation. Since I am in a different timezone, I would like to see the bucketing done from midnight to midnight in my timezone.

I’ve been looking for solution to this problem and the closest topic I can find is: https://github.com/grafana/grafana/issues/6222
However the datasource they are using is Elasticsearch.

2

Did you ever find a solution to this @leonvking0? I’m looking for a solution to the exact same issue.

3

Not really…I hard coded one hour shift to mitigate the problem

1 Like
4

I have the same problem too. May I know how do you fix this problem? This is a very typical problem and I wonder why there is no “official” solution.