DateTime manipulation in couchbase analytics (SQL++)

Analytics
,
1

Hi, I am playing around with couchbase analytics for past few days and faced following problems related to queries.

Problem 1: I read somewhere that N1QL is proper subset of SQL++ Specification. But, I am not able to find any functions related to datetime manipulation like N1QL has (like Date_format_str, Date_diff_str) etc.

Problem 2: Also, I am trying to create an index on a datetime field (startDate)
with query:

CREATE INDEX IX1 ON movements(startDate:DATETIME,firstName:STRING,lastName:STRING)
;

when I specifIed the field’s datatype as datetime, the query threw an error saying :

Cannot index field [startDate] on type datetime. Supported types: bigint, double, string

So I tried

CREATE INDEX IX1 ON movements(startDate:STRING,firstName:STRING,lastName:STRING)
    ;

And it executed successfully but when I queried firstName and lastName within a particular date range, no result is coming even when data is there for that condition. This obviously has to do with the STRING type that I specified in the Create Index statement which makes it unusable for this use case.

So, my question is, why only these three datatypes are supported in create index statement and not datetime?
As we know that a huge number of queries filter data on date range filters.

Problem 3: Where clause in CREATE INDEX statement is not working.

Query:

CREATE INDEX IX_Name_movements ON movements(firstName:STRING,lastName:STRING) Where (personTypeCode = 'RG');

Response:

 {
    "code": 1,
    "msg": "Syntax error: In line 1 >>CREATE INDEX IX_Name_movements ON movements(firstName:STRING,lastName:STRING) Where (personTypeCode = 'RG');;<< Encountered \"Where\" at column 81. ",
    "query_from_user": "CREATE INDEX IX_Name_movements ON movements(firstName:STRING,lastName:STRING) Where (personTypeCode = 'RG');"
  }

Please let me know if I’m doing something wrong here.

2

Hi @krishan.jangid ,

you are running into some limitations of the developer preview.

Problem 1:
While the query language is quite complete, the function library is not there yet. The datetime functions that you know from the query service will be available in a release later this year.

Problem 2:
The indexes in the analytics service support only the types that are available in our (JSON-based) datamodel. The currently supported atomic types are string and boolean and the 2 numeric types bigint and double (as
in the query service). Datetime values are represented as strings in the ISO 8601 format (again, as it is done in the query service). For these stings the string comparison can be used to describe range filters and thus the string indexes can be used in this case as well.

Problem 3:
Filtered indexes are not yet supported, but filtered (shadow) datasets are. Can your case be handled with filtered datasets? Otherwise a current option is to use an unfiltered index. While unfiltered indexes will be bigger than the filtered ones, this might be unproblematic as secondary indexes in the analytics service are colocated with the primary data and distributed.

Does this help?

We appreciate your feedback on the developer preview very much.
Please continue to tell us about the problems that you run into.

Thanks!

3

Hi, Thanks a lot for this information. The workarounds you suggested will work for now.
I have two more questions though.
Q 1. Will the problem 3 be soleved in next realease as well?
Q 2. How can I calculate age from birthdate when I don’t have any date-time function available?

I apologize for asking too many questions on a single thread. Will ask upcoming queries on a separate post.
Also, do let me know the expected release date for next version.

4

Hi @krishan.jangid,

for Q1:
I’m sorry, but this is not on the plan for the next release.

for Q2:
You could

  • extract the data components using string functions and then
  • cast to a numeric type to do arithmetic.

E.g.

select year, month, day
from [ "2017-03-01" ] as date
let year  = to_bigint(substr(date, 0, 4)), 
    month = to_bigint(substr(date, 6, 2)),
    day   = to_bigint(substr(date, 9, 2))