Problem with SQL queries with time intervals

InfluxDB 2
1

Hello all,

I’m trying influxDB Cloud Serverless and I have some issues specifying the time intervals in my SQL queries.
The first is that it looks like the resolution of the query is only of minutes, although I specify hours, minutes and seconds, because if i write:

WHERE
time > ‘2024-05-10T00:00:00Z’ AND time <= ‘2024-05-11T00:00:00Z’

The query does not include the data from ‘2024-05-10T23:59:34Z’ . The only way to get this data point is to extend one minute the query time interval to time > ‘2024-05-10T00:00:00Z’ AND time <= ‘2024-05-11T00:01:00Z’

The other weird issue is that for the particular interval of: “time > ‘2024-05-12T00:00:00Z’ AND time <= ‘2024-05-13T00:00:00Z’”, when I make the query, the first row of the table is the data corresponding to ‘2024-05-13T00:00:00Z’ which it should be the last data point. Ther rest of the rows are nicely ordered and the data point that its missplaced looks right (meaning its value makes sense if moved to the end of the series).

Any suggestions on how to address these issues will be welcome.

2

@victormm

  • Which InfluxDB product are you using? InfluxDB Cloud Serverless?
  • Can you provide your full query?
1 Like
3

Hi scott,

Thanks for your attention!

Yes its InfluxDB Cloud Serverless.

The full query is:
SELECT *
FROM “Clima_SCT_Instituts”
WHERE
time > ‘2024-05-12 00:00:00’ AND time <= ‘2024-05-13 00:00:00’

(I tried also with “time > ‘2024-05-12T00:00:00’ AND time <= ‘2024-05-13T00:00:00’” with the same results)
The weird thing is that I have three measurements and query all three of them one after the other and sometimes I get the right order for the last data row for one or two of them but not for the others. By now I solved the problem by sorting the data by timestamp before using it but I don’t think that should be necessary…

4

You’re right, sorting shouldn’t be necessary in this kind of query. Do you mind submitting a bug report on the InfluxDB GitHub repo? If you include a sample data set and test against as well, that would really help. Thanks!