Group by time in outside of UTC/GMT

1

I have a database on InfluxCloud and I’m trying to group time data. The data is stored in UTC time of course, but I want to group it by day for various time zones. If I specify where time <= epoch of midnight in the appropriate time zone and do a group by time(1d) it seems to choose midnight GMT and group that way. I tried grouping by 86400010ms (1 day plus 10ms) and that seems to work. I also tried grouping by 24h, 1440m, etc and all of those had the same outcome as 1d.

Is there a way to group by a 1 day period and force Influx to use my start time as the grouping start point?

2

In v1.2, group by time now has an optional offset parameter that I think would do what you’re looking for.

group by time(1d, -8h)

In the upcoming v1.3, there will be basic timezone support.

3

We are working on a way for you to leverage timezone as part of InfluxQL.
Essentially, we are working on the feature here: Support timezone for a user-friendly way of setting the timezone offset · Issue #6541 · influxdata/influxdb · GitHub

It is planned as part of InfluxDB 1.3.0 – slated for this summer.

4

@tim.hall: Following up on this, is there now a way I can query InfluxDB to show daily rollups using a GROUP BY clause that’s timezone-aware? i.e. always GROUP BY 1 day, starting from midnight in my local timezone?

5

You can speficy a time zone offset since 1.3.4 and there were some bug fixes in 1.4.0.

-- select from measurements grouped by the day with a timezone
SELECT mean("value") FROM "cpu" GROUP BY region, time(1d) fill(0) tz('America/Chicago')