add time operator today()

[beckettsean]

Feature Request

Proposal: [Description of the feature]

today() is a shortcut to midnight UTC of the current day. E.g. if it is September 19th, 2016, then today() returns 2016-09-16T00:00:00Z

Use case: [Why is this important (helps with prioritizing requests)]

Was brought up as a desired feature in the Austin 1-day class.

@desa can you elaborate?

[desa]

Not sure of the exact use case, but the main interest seemed to be for doing queries on the previous day or days, but disregarding the data from today.

[beckettsean]

Ahh, so something like

SELECT mean(value) FROM example WHERE time > today() - 12h AND time < today()

Seems like it would be more useful if we had timezone support, but it also seems like an easy one to add.

[desa]

yup

[mark-rushakoff]

Even if this only supports UTC "today" at first, we need to consider how the API would change if/when we support localized today. Perhaps an argument like today(-8) to specify today as UTC minus 8 hours.

[mcules]

+1

[cryogenx]

+1

[AtoxIO]

+1

[jaumzors]

+1

[huji0624]

+1 Does anyone know if this feature has been implemented or not?

[reloxx13]

still waiting :S

daily statistics with now() -1day will cut off data after xx:xx:xxh (from now time eg. 20:26:01h) from previous day cause -1day is -24h and not -todayHoursDone, not showing the max data of the prev day if it raises after that now time.

eg. past 7 days total blocked domains in pi-hole.

[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.

[reloxx13]

unstale

[Pwuts]

bump, really wondering why this is not implemented yet, would be an awesome feature

[PatrickGlatz]

Also would highly apprectiate this feature +1

[Fux2]

I am also looking for this feature +1 !

[eabullard]

A +1 here as well.

[Jollyjohn]

+1 for example, I have an IoT device recording the amount of water used. Each time the meter rotates 1 revolution a measurement of +1L is recorded. I want to be able to find out how much water has been used today and want this to be from midnight till now. I don't want the past 24h, I need today and today only. Without this feature, I'm not quite sure how to construct the query...

[think-free]

+1 for example, I have an IoT device recording the amount of water used. Each time the meter rotates 1 revolution a measurement of +1L is recorded. I want to be able to find out how much water has been used today and want this to be from midnight till now. I don't want the past 24h, I need today and today only. Without this feature, I'm not quite sure how to construct the query...

Same here, with a solar inverter. Want to know the today max/min/total production, the max/min produced in the past (without today) Can't find a workaround ...

+3 years this issue was openned 😣

[pierrejay]

+1

[nkitanov]

I also need that.

[mouckatron]

+1 me too

[uncle-fed]

+1

[amadeuspzs]

+1

[JsBergbau]

This featurerequest is now almost 4 years old. A lot of people, including me, need it. Please implement it soon :) An the meantime I have to create the query "external" something like select * from Table where time >= '2020-06-13' and time < '2020-06-13' + 1d

[OxymoRonald]

+1

[one4many]

+1

[geralltgriffiths]

Please can I add my request to this, too? Use-case is simple min and max temperature and ultraviolet levels per calendar day (so far) from my weather station. Tempted to play with a variant of dusk and dawn times, based on (heavily damped) ambient light-level changes too. Thanks in anticipation.

[m-knopp]

+1, I could currently use this

[MarcoA12000]

+1

[tcopple]

+1

[alexose]

+1

[jandramila]

+1

[brendanpward]

+1

[Satforst]

+1

[brontide]

Count me in, trying to find a way to align a graph to 24h starting at midnight and this seems to be the missing link.

[alsargent]

@beckettsean Since 2016, we've been able to run InfluxQL queries such as:

SELECT max("potato") FROM "tomato" WHERE time < now() + 1000d

... outlined in this blog post. What specific queries are you looking to run?

[tcopple]

@alsargent I believe the aim of this ticket is implement a query feature that excludes today's value in favor of the previous day. @reloxx13 summed it up I think.

[bosvos]

+1 - also for solar inverter production calculations.

[esin]

Waiting for this feature, but I think solved that problem, without today() operator My goal is - find how much water count today from 00:00 till current time I have table iot_data, with fields time, type and currentvalue

1. Grouping data by days
2. Find last max value (or last value. Due that I'm looking for water count - I need max value) for last 2 days - today and yesterday
3. And finding difference between values (min will give me yesterday value - last value at 23:59 yesterday, max value - current)
4. Need two ORDER BYs due to InfluxQL

SELECT MAX(vals)-MIN(vals) from (SELECT * FROM (select max(currentvalue) as vals from iot_data where type='cold' and time > now() - 7d GROUP BY time(1d)) ORDER BY time DESC LIMIT 2 ) ORDER BY time DESC

[SyncM1972]

+1 Check out how nicely relative time formats can be stated in php. Also grafana provides improved time range controls. This would make many queries much more straight forward.

[Nobody84]

+1

[patbou74]

exist in flux, see https://docs.influxdata.com/flux/v0.x/stdlib/universe/today/ But not exist in InfluxQL

+1, I could currently use this

[hob]

+1 I've ben struggling with this limitation as well

[mb91]

Me too. It would help me to visualize the energy generated by my solar panels every day.

[sanderson]

This could probably be added to today() in the the stdlib, but other functions depend on it so the update is a little more far reaching. But as a stop-gap, you can define your own today() function with a tzoffset parameter:

import "date"

today = (tzoffset=0h) => date.add(to: date.truncate(t: now(), unit: 1d), d: tzoffset)

from(bucket: "example-bucket")
    |> range(start: today(tzoffset: 1h)

Currently in Flux, setting the timezone only affects how windows are created across time-shift boundaries (daylight savings, British summer time, etc.). It doesn't affect this type of operation.

Edit: This is possible if you're using Flux. This is not possible with InfluxQL.

[gonzalu]

import "date"

today = (tzoffset=0h) => date.add(to: date.truncate(t: now(), unit: 1d), d: tzoffset)

AWESOME THANK YOU!!!!!!

is there a way to remove the TIEM and leave only the DATE? I can remove the DATE with an override function but not the time.

Thanks again.

[sanderson]

@gonzalu Flux time types are represented by RFC3339 timestamps (YYYY-MM-DDThh:mm:ssZ). To return only the date-part, you need to convert the time type into a string and trim the time off. You'll need to do this as a separate column:

import "strings"

data
    |> map(fn: (r) => ({r with date:  (strings.split(v: string(v: r.time), t: "T"))[0] }))