Update - complete documentation for the `IN` operator

[amyshwang]

Describe the requested changes:

The IN operator has some extra features. The documentation suggest we can add an extra modifier using semicolon as the separator to increase/decrease the time range. What it doesn't say is you can actually add extra parameters (not sure what the official name for those is) to repeat the filter in subsequent time periods.

Now with an example (you can run this at https://demo.questdb.io/)

Basic IN works like this and would search anything within that particular day

select pickup_datetime from trips WHERE pickup_datetime IN '2018-06-01T13:00:00'

Now I can extend to search from that timestamp and extend to an extra point in time, let's say 1 minute

select pickup_datetime from trips WHERE pickup_datetime IN '2018-06-01T13:00:00;1m'

So far, so good. All of this was documented. Now imagine I want to search records within the time interval of that minute, but in consecutive days. This is supported with this syntax:

select pickup_datetime from trips WHERE pickup_datetime IN '2018-06-01T13:00:00;1m;24h;10'

Or the equivalent

select pickup_datetime from trips WHERE pickup_datetime IN '2018-06-01T13:00:00;1m;1d;10'

What we are doing here is saying: I want to repeat this filter for every 24h (in the first query) or 1d (in the second query) and do this 10 times. The result of both queries above would be getting all the trips from 13:00 to 13:01 from 2018-06-01 to 2018-06-10.

Let's see what happens If we change the 24h/1d parameter to 12h, like this

select pickup_datetime from trips WHERE pickup_datetime IN '2018-06-01T13:00:00;1m;12h;10'

In this case it would give us results for each interval from 13:00 to 13:01 and also from 01:00 to 01:01, since we are repeating every 12h. Since we are doing this 10 times, we would be getting results for 5 days (10 intervals of 12 hours), finishing on 2018-06-06T01:01:00

Note: This is not supported if the modifier is a negative range

Location:

https://questdb.io/docs/reference/sql/where#time-range

[TheZal]

Can work on that!

[amyshwang]

🚀 It's yours!

[amyshwang]

1. Multiplier cannot be negative?

SELECT pickup_datetime FROM trips 
WHERE pickup_datetime 
IN '2018-06-02T13:00:00;-1s';

Invalid date This is different from our Docs

2. Occurrence can be negative?

Some observation:

select pickup_datetime from trips 
WHERE pickup_datetime 
IN '2018-06-01T13:00:00;1s';
-- 13 rows
select pickup_datetime from trips 
WHERE pickup_datetime 
IN '2018-06-02T13:00:00;1s';
--14 rows

select pickup_datetime from trips 
WHERE pickup_datetime 
IN '2018-06-01T13:00:00;1s;1d;2';
-- 27 rows, a combination of the above 2 queries

select pickup_datetime from trips 
WHERE pickup_datetime 
IN '2018-06-02T13:00:00;1s;-1d;2';
-- 0 rows (but what does this even mean?)

select pickup_datetime from trips 
WHERE pickup_datetime 
IN '2018-06-02T13:00:00;1s;-1d;1';
-- 14 rows, same as IN '2018-06-02T13:00:00'

select pickup_datetime from trips 
WHERE pickup_datetime 
IN '2018-06-01T13:00:00;1s;1d;-2';
-- 13 rows but this does not make any sense

I use the demo site to run the above.

[ideoma]

Negative values are not yet supported

https://github.com/questdb/questdb/issues/2509

[amyshwang]

@TheZal I am assigning this to myself as we have not received any PRs from you. Please let me know if you have a WIP.

[TheZal]

Sorry, i was busy with other things and didn't even start :/