Weird results with group by time()

Store
,
1

Hi,

Iam getting crazy with this, any help is appreciated.
I have a running collection over currency pairs and their closing prices.
In the pasted output the prices are aggregated into 5 min values.

Please look below for my influx cli output:

select ccypair, close from “5m_bars” where (ccypair = ‘AUDCHF’ or ccypair = ‘AUDCAD’) and time > now()-30m

name: 5m_bars
time ccypair close

2018-10-08T18:20:00Z AUDCAD 0.91683
2018-10-08T18:20:00Z AUDCHF 0.70231
2018-10-08T18:25:00Z AUDCAD 0.9171
2018-10-08T18:25:00Z AUDCHF 0.70238
2018-10-08T18:30:00Z AUDCAD 0.9171
2018-10-08T18:30:00Z AUDCHF 0.70246
2018-10-08T18:35:00Z AUDCAD 0.91697
2018-10-08T18:35:00Z AUDCHF 0.7026
2018-10-08T18:40:00Z AUDCAD 0.91703
2018-10-08T18:40:00Z AUDCHF 0.70259
2018-10-08T18:45:00Z AUDCAD 0.91697
2018-10-08T18:45:00Z AUDCHF 0.70265

— Just to show some sample data from the measurement

select first(close) as close into “5m_bars_tmp” from “5m_bars” where time > now()-15m group by time(5m,-1m),ccypair fill(previous)

name: result
time written

1970-01-01T00:00:00Z 452

— Create a new measurement looking back 15 minutes and offset -1m

select ccypair, close from “5m_bars_tmp” where ccypair = ‘AUDCAD’

name: 5m_bars_tmp
time ccypair close

2018-10-08T18:34:00Z AUDCAD 0.91697
2018-10-08T18:39:00Z AUDCAD 0.91703
2018-10-08T18:44:00Z AUDCAD 0.91697

select ccypair, close from “5m_bars_tmp” where ccypair = ‘AUDCHF’

name: 5m_bars_tmp
time ccypair close

2018-10-08T18:29:00Z AUDCHF 0.91697
2018-10-08T18:34:00Z AUDCHF 0.7026
2018-10-08T18:39:00Z AUDCHF 0.70259
2018-10-08T18:44:00Z AUDCHF 0.70265

This is where it goes weird. AUDCAD is alphabetically the first ccypair in the measurement and AUDCHF is the 2nd.

Why is there no value with time 18:29 on AUDCAD ? and why is there a value from AUDCAD on AUDCHF at 18:29 ? As there is clearly a value for AUDCHF on 18:30 in the original measurement.

What am I doing wrong ?

Thanks!

//p

2

I’m really not sure and I don’t have time to test but I’ll suggest something. Maybe it’s related to:

select first(close) as close into “5m_bars_tmp” from “5m_bars” where time > now()-15m group by time(5m,-1m),ccypair fill(previous)

I think the issue is where time > now()-15m and group by time(5m,-1m). If you group by 5m and offset -1m, you actually need to read from 16m in the past to 1m from now() to return 3 results.

Try:

select first(close) as close into “5m_bars_tmp” from “5m_bars” where time >= now()-16m AND time < now()-1m group by time(5m,-1m),ccypair fill(previous)

When using fill(previous), if the first “group by” block of time does not contain any data it returns nothing.

3

Same result.
Been playing around with that and I get same results no matter what the time back is.
If i chose -1h then the first value I get for AUDCHF is a value from AUDCAD on now()+1h… to make it weirder…

4

That looks like a bug. The offset function is probably used less often than everything else so it’s more likely to have an undetected bug.

5

Just tried without the offset and I get the same results.
So, I must be doing something wrong somewhere ?

6

i have seen issues with > time - 30m … try using and time < now + 30 minutes (into the future)

7

Uhm… not sure I follow you on how to do this ?

(forgot about this thread, hence the month long silence)…

8

instead of using just

time > blah

use

where time > past_time and time < future_time

where future time is something like 30 minutes after now

9

is ccypair a field or a tag?