DATE_DIFF_STR vs. >=

SQL++
,
1

Hello,

I am trying to query the database and have to query the transactions from last 10 days. There are 2 options that I can think of doing this considering today’s date is 08/24.

  • Use DATE_DIFF_STR and give 10 as an input to the function to get the last 10 days transactions

  • I also have a ‘date’ field in the document. I can write a query like this: where type = 'doc_type' and date >='2021-08-14'

Which of the above 2 options would be efficient? Using DATE_DIFF_STR or using >= operator?

Kindly help.

Thanks,
Devansh

2

If you can guarantee that the dates are comparable (meaning they are stored in year/month/day format), then a plain string comparison would be faster.
You can verify that for yourself by turning on execution profiles, and checking the service time for your scan operator (if the filter is being pushed down to the indexer), and the execution time for the filter operator.

3

Perfect! Yes dates are comparable. Thanks a lot for the information!

4

Instead of DATE_DIFF_STR(), index on date and use constant other side so that predicate can pushed to indexer. date >= DATE_ADD_STR(NOW_STR(“1111-11-11”),-10,“day”) OR date >= “2021-08-14” OR date >= $date (named parameter)

CREATE INDEX ix1 ON mybucket(date) WHERE type = "doc_type";
SELECT ....
FROM mybucket AS b
WHERE `type = 'doc_type' and date >= "2021-08-14"