Selecting data with offset results in slow query


#1

Good day,

I’ve got a very simple setup. I’m inserting cryptocurrency prices into my measurement:

time                price   symbol
----                -----   ------
1551709629137423000 46      LTCUSD
1551709629139537000 3736.32 BTCUSD
1551709629157560000 0.3045  XRPUSD
1551709632229084000 0.3046  XRPUSD
1551709635291219000 3736.46 BTCUSD

I’m trying to group the data into 15 minute bars with the following query:

select first(price) AS open, last(price) AS close, max(price) AS high, min(price) AS low from ticks where symbol = 'BTCUSD' group by time(15m), symbol

This works fine and I’m getting the following result:

time                open    close   high    low
----                ----    -----   ----    ---
1551708900000000000 3736.32 3738.37 3738.37 3736.32
1551709800000000000 3738.11 3738.66 3738.66 3737.64
1551710700000000000 3736.66 3736.66 3736.67 3736.66

However, now I want to query 15 minute bars, but with an offset of +1 minute. So I want the bars to start at minute 1, 16, 31 and 46. As far as I understand I can achieve it with ‘group by time(15m,1m)’ but when I do that my query times out and never returns any records.

Any ideas on what I’m doing wrong?


#2

Hi Rickman ,

I have done a simple test and I have also a timeout , no results …
I guess it is a bug ?

I have found a workaround , if you add “and time > now() -3w” for example it works

I have stopped my ‘bad’ query with ctrl-c and that shows that the query starts from 5 october 1694
2 weeks before the British/American colonial forces, led by Sir William Phips, fail to seize Quebec from the French.

ERR: ll],[“1694-10-05T02:08:26.290448384Z”


#3

Ok I figured it out myself. I needed to include an additional where statement stating from which point in time I wanted to query, because otherwise it would try to fill from the beginning of time :wink:


#4

MarkV: Yes you’re correct. It seems like a bug which causes it to fill the result set from 1694 till now. Setting a start date is a good workaround for me.