Index based on timestamp

SQL++
1

Hello,

I have a document which has an Effective Timestamp field which is a String, formatted as a GMT timestamp. The bucket is expected to have millions of records. I have a covering index on the effective timestamp .

The documents in the bucket have a very long retention period of around a year. However there is also a use case to query only last 7 day data. Is is possible to create another index based on effective timestamp between current date and last 7 days? If so, will this help improve the query performance given that I already have this field as part of my covering index?

A sample select query is as below -
select * from testbucket where docType=‘transactiondoc’ and effTS between date_add_str(now_tz(‘UTC’), -7, ‘day’) and now_tz(‘UTC’)

I am trying to create an index as below, but its giving me expression not indexable. What would be the correct way to create such an index?
create index idx_1 on testbucket(effTS,field1,field2,field3) where effTS between date_add_str(now_tz(‘UTC’), -7, ‘day’) and now_tz(‘UTC’)

2

now_tz() is volatile and can’t be used in index.

You should be able to use same index that you created long retention period by specifying the range of dates.

create index idx_1 on testbucket(effTS,field1,field2,field3) WHERE docType="transactiondoc" ;
SELECT * FROM testbucket WHERE docType="transactiondoc" AND
effTS BETWEEN date_add_str(now_tz("UTC"), -7, "day") and now_tz("UTC");

If You do EXPLAIN you should see something like below,
This indicates indexer positions at low value and stops at the high value and produce results in between.

                       "spans": [
                            {
                                "exact": true,
                                "range": [
                                    {
                                        "high": "now_tz(\"UTC\")",
                                        "inclusion": 3,
                                        "low": "date_add_str(now_tz(\"UTC\"), (-7), \"day\")"
                                    }
                                ]
                            }
                        ]
3

I am facing similar issue

CREATE INDEX notifications-projectid-days ON notifications(createdTime) where createdTime > DATE_ADD_MILLIS(CLOCK_MILLIS(), -7, ‘day’)
It throws an error saying - “Expression not indexable: (DATE_ADD_MILLIS(CLOCK_MILLIS(“UTC”), (-7), “day”) < createdTime) - at end of input”
I read somewhere that CLOCK_MILLIS cannot be used to created index, is there any other way to get the current system time to create the index?

But the select statement with same where clause works
Please help me with this.

4

CLOCK_MILLIS() is variant and you can’t use in the index.

Remove WHERE clause or use constant in index WHERE clause. Query can use CLOKC_MILLIS()