today() not working in query where statement (now() works ok)

[jeroenst]

Steps to reproduce: List the minimal actions needed to reproduce the behavior.

1. use today in a query where (example: SELECT difference(last("value")) FROM "solar.grid.total.kwh" WHERE time >= today() GROUP BY time(1h) fill(0))
2. ...
3. ...

Expected behavior: query works like when using now() in where statement

Actual behavior: Error: Invalid operation: time and *influxql.Call are not compatible

Environment info:

• System info: Linux 5.4.0-77-generic x86_64
• InfluxDB version: InfluxDB v1.6.4 (git: unknown unknown)

Config: Copy any non-default config values here or attach the full config as a gist or file.

Logs: Include snippet of errors in log.

Performance: Generate profiles with the following commands for bugs related to performance, locking, out of memory (OOM), etc.

# Commands should be run when the bug is actively happening.
# Note: This command will run for ~30 seconds.
curl -o profiles.tar.gz "http://localhost:8086/debug/pprof/all?cpu=30s"
iostat -xd 1 30 > iostat.txt
# Attach the `profiles.tar.gz` and `iostat.txt` output files.

[williamhbaker]

Thanks for the bug report.

I've confirmed that this occurs on the latest 1.x OSS (1.8.10), as well as on 2.1.1 using the /query endpoint.

[jeroenst]

Thank you for verifying and confirming.

[de-arl]

Same problem with "InfluxDB shell version: 1.8.3":

> select * from my_measurement where time > today() - 1d
ERR: invalid operation: time and *influxql.BinaryExpr are not compatible

> select * from my_measurement where time > today()
ERR: invalid operation: time and *influxql.Call are not compatible

[timhallinflux]

today() was not part of the InfluxQL spec. Adding this would be an enhancement.

today() was added for Flux. https://docs.influxdata.com/flux/v0.x/stdlib/universe/today/

[bugla]

Just stumbled upon the same issue today: In InfluxQL both now() and today() return a time data type. The result of now() can be used in time calculations (inside a SELECT query's WHERE clause) like time >= now() - 7h whereas time >= today() - 7h strangely does not work, resulting in "InfluxDB Error: invalid operation: time and *influxql.Call are not compatible".

I call this a bug. ... no matter if "today()" was originally in the InfluxQL spec or not, as stated in the last comment. It is in the InfluxQL documentation since a long time and as long as it is not part of a calculation, it also works fine, so someone clearly implemented it (which is good), but for some strange reason a calculation with the resulting time object fails (which shouldn't even have anything todo with the today() function itself, because at this point it's just a time object/instance), so from my point of view that's pretty clearly a bug.

I would also love to see this fixed, as it's a serious issue, if you need to compare different measurement results of specific times on different days and need that in a time-of-day-constant sliding time window (which is something very useful, e.g. in testing scenarios), e.g. if you want to see a comparison of measurement results between 7am and 8pm for the last 3 days in an output. You can't base this on now() as this has the full time in it, so you'd land on a time window of the data that depends on the moment you executed the request (incompatible with all individual query / query on demand use cases), and there's no truncate(now(), 1d) in InfluxQL, and there's also no (correct me if I'm wrong) selection of math functions or string conversion + manipulation functions you can use inside a WHERE clause to achieve that rather simple goal (I think I've read pretty much everything relevant in the docs and Stackoverflow, but haven't found relevant functions that could work in InfluxQL WHERE clauses to make the wanted result happen. If you read this and know how for sure how to do this, please point me to it).

... and not everyone can easily convert a whole large project from InfluxQL to Flux (in case that works there), so fixing this bug in InfluxQL would be very much appreciated.

[AxelPatron]

3 years.....

[ThePowerOfElectricity]

Make that 4.

[NicoBerlo1]

me too