Understanding the difference in timeformat comarison in n1ql query

SQL++
1

I am trying to understand from the performance point what would be the difference . If I am comparing the time field as mentioned below ways

SELECT something from bucket b where b.fieldTimeStamp >= ‘2020-02-10 08.00.00’ and b.fieldTimeStamp<='b.dbdTriggerTimestamp >= ‘2020-02-10 09.00.00’

where the above time mentioned is always passed as current time and current time + 60 minutes

SELECT something from bucket b where b.fieldTimeStamp between date_format_str(CLOCK_TZ(‘GMT’, ‘1111-11-11 11.11.11’),‘0’,minute)’ and date_format_str(CLOCK_TZ(‘GMT’, ‘1111-11-11 11.11.11’),‘60’,minute)’

adding to it … does it scan the entire bucket and impact performance if I add the below line to the queries

b.fieldTimeStamp IS NOT MISSING

Any help is very much appreciated

Note : bucket consists of 500K to 2 million records at any given point of time

2

Why do you need to add b.fieldTimeStamp IS NOT MISSING?
Once you have predicate b.fieldTimeStamp BETWEEN it implies the b.fieldTimeStamp IS NOT NULL.
The performance wise you may not see much difference. But each document the predicate is evaluated so it i needs few cpu cycles.

If you do addition of 60 minutes on value like below it will NOT scan entire index.

CREATE INDEX ix20 ON default(fieldTimeStamp);
 EXPLAIN SELECT something 
FROM default b 
WHERE b.fieldTimeStamp BETWEEN UTC_NOW() AND DATE_ADD_STR(UTC_NOW(),60,"minutes");

Check the spans of EXPLAIN statement it gives details.

3

Thanks @vsr1 for your reply . The n1ql query taking more time when querying with ‘IS NOT MISSING’ and without that its very fast and my understanding from your reply is that the ‘between’ doesn’t makes much difference when comparing the timestamps .
Adding to the other suggestion but the ‘addition of 60 minutes’ is a configured value when querying and might change when there is a lot of data

4

If you give only IS NOT MISSING it needs to get all the data. If you give specific dates and you right index it only gets that range. You query can configure if you want 60, 120,300 upto you. As range increases data increases and can take longer.

if timestamp is ISO-8601 format (https://docs.couchbase.com/server/current/n1ql/n1ql-language-reference/datefun.html) you can use between, there is no limitation and it will work

You can check where time spent through.


Optimize N1QL Performance Using Request Profiling

5

Thanks a lot @vsr1 for the clarification and suggestion