Influxdb: Add new time operators

[pauldix]

We should give users the ability to query times using operators other than now(). These would be some good additions:

• today() - sets time to now() rounded down to start of day
• yesterday() - sets time to now() - 1d rounded down to start of day
• monday() - the start of the previous monday(). Do this for every day of the week
• week() - the start of this week. Could take an argument for what day the week starts on
• month() - month boundaries

This won't go into 0.9.0, but can make it into a point release. Unless someone in the community wants to get crazy and implement it. For the adventurous you should look in these two places:
https://github.com/influxdb/influxdb/blob/master/influxql/engine.go#L583
https://github.com/influxdb/influxdb/blob/master/influxql/ast.go#L2192-L2200

[corylanou]

How do you want to handle timezones?

[corylanou]

Oh yea, and Daylight Savings Time :-).

[nemesisdesign]

could influxdb use the date set on the server? Or would you rather put it in the config file?

[XANi]

or just have separate operator for it for example today(tz("PDT")) or more generic today() + tz("PDT")

That could also be handy for using "in reverse" for example if for some reason input data TS was in certain timezone, calculating GMT time would just require field - tz("PDT")

Or converting between them, say from PST to CET: field - tz("PDT") + tz("CET")

[pauldix]

I almost feel that the timezone could be a function argument on the end of the query. Since it can be applied to either the group by time or the relative time functions. And you'd always want it the same for both. So maybe:

select mean(value) from cpu
where time >= today()
group by time(10m)
tz("PST")

Where the tz function could take the string name of a time zone or it could take a positive or negative integer for offset from UTC.

[toddboom]

I agree with @pauldix on making it a function argument. I think doing time zone arithmetic is a little overkill, and can be solved by doing the explicit integer difference (i.e. +3/-3). Can we call it time_zone instead of tz?

This already came up in #388, but I'm thinking we should make a new issue to keep the discussion clean. Moving this to #2074.

[atombender]

I just encountered the problem of week binning: InfluxDB seems to assume to weeks start on Sundays, but the ISO standard and much of the world uses Monday (the US is an outlier). This needs to be configurable.

The fact that time is synctactically expressed as a partially-applied function makes this a bit awkward unless you introduce position-independent named arguments. How about taking a leaf out of SQL's book and introducing some readable keywords?

...
group by time
  time_zone 'CET'
  bin_by '1w'
  week_start 'monday'

[pqvst]

A good source for inspiration might be the moment.js library (I find it very intuitive and simple when it comes to parsing and manipulating time). http://momentjs.com/docs/#/manipulating/.

The startOf and endOf operators on dates would be a very generic approach to calculating timespans like "this month", "last month", etc.

These could be implemented as alternatives to now():

• now() => current date/time
• day() => start of current day (2015-10-08 00:00:00)
• month() => start of current month (2015-10-01 00:00:00)
• year() => start of current year (2015-01-01 00:00:00)
• etc...

Now we can easily build more complex time spans like:

• today: time >= day() and time < day() + 1d
• yesterday: time >= day() - 1d and time < day()
• this month: time >= month() and time < month() + 1m
• this day last month: time >= day() - 1m and time < day() - 1m + 1d

In these cases, however, addition and subtraction of time/dates would have to be correct (due to leap-time, varying number of days in months, and all other messy date/time things). I'm not sure how that is implemented at the moment.

month() + 1m is not equivalent to month() + 30d
day() + 1d is not equivalent to day() + 24h
...

Furthermore, some of these time ranges could be implemented for us:

last_month() => time >= ... and time < ...

I might take a look at this if no work is being done on it. @pauldix, the first link you suggested is a 404. Has the filename changed?

[stephanstapel]

+1

[mvadu]

Was it partially implemented some time back? I am running against 0.9.5, and can use time > today() in my queries.

[Lahorde]

+1

Now, how can I do to get a count of values for today?

If I do :

select count(value) from series where time >= now() - 1d
-----------------------------------------
time                            count
2016-06-15T11:53:17.649981214Z  34

I won't get count from 00:00

If I do :

select count(value) from series where time >= now() - 1d group by time(1d)
-----------------------------------------
time                    count
2016-06-15T00:00:00Z    12
2016-06-16T00:00:00Z    22

I will get two points as result
Correct value is 22 but how can I select only this value using influxdb?

I've tried subqueries :

   select last(count) from (select count(value) from series where time >= now() - 1d group by time(1d))

but it is not yet implemented :
https://github.com/influxdata/influxdb/issues/52

I've also used limit operator :

 select count(value) from  series where time > now() - 1d group by time(1d) limit 1
-----------------------------------------
time                    count
2016-06-15T00:00:00Z    2

but limit is done on count value!

[jwilder]

Related #1154 #3290

[kostasb]

Additional time operators should also take part in GROUP BY time clauses, such us group by time(1M) for monthly grouping.
This will enable queries to group results using actual calendar broundaries.
E.g. group by time(30d) is not necessarily equivalent to 1 calendar month, depending on how long a calendar month is.

[nirpodo]

+1

[warezWally]

+1

Has any traction been made on this? I Would adore this feature.

[jsternberg]

Related to #6541 and I have a PR ready to add some of these time operators after #7762 is merged.

[ryanmills]

+1 for time(1M)

[krise3k]

@jsternberg #7762 has been merged, so I send kindly reminder about this feature :)

[willguxy]

+1 and would be great to have time of the day sort of operations. e.g.

• hour()
• minute()
• second()

For 2017-01-01 10:03:21 the above should return 10, 3, 21 respectively

[omki2005]

+1 Also would appreciate having time of the day operations. We want to be able to group data between say 9AM and 10AM and apply a different tag to "hour-of-day"

[Naesstrom]

+1 on this, would love to get yesterdays max value for example!

[aldas]

to be able to use relative time for at least 'start of today' (UTC is fine) in query would be really helpful. Atm you need to calculate this stuff in application code and use absolute time in query.

[sebastien85]

+1

[MitchVL]

+1

[liangshengzh]

+1

[superlevure]

Is there any news on this feature request ?

[alexanderturner]

+1

[noppelmax]

+1

[jpldev]

+1

[stale[bot]]

This issue has been automatically marked as stale because it has not had recent activity. It will be closed if no further activity occurs. Thank you for your contributions.

[stale[bot]]

This issue has been automatically closed because it has not had recent activity. Please reopen if this issue is still important to you. Thank you for your contributions.

[sibbl]

I'm not sure what I dislike more, these "+1" messages or the stale bot closing issues if you don't write "+1" 🙄

Please reopen this, I'd love to have time operators and keep this tracked here.

[jsternberg]

I apologize that the closing of this issue happened because of a bot, but the current plan is these will be implemented within Flux and there are no current plans to implement this in influxql.

[anderl80]

I'd be delighted to have more time operators, too!

[tsomod]

Any progress on this? Will this be a feature of Flux? If yes, when will the feature be introduced? I see it is not a part of Flux as of yet.

[ccall48]

Would also love this to be added as a feature.

[jsternberg]

Going to be tracking this here: https://github.com/influxdata/flux/issues/1549