Date Index

SQL++
1

I know there are more date functions coming in DP3, but functions don’t help as access points. Any thoughts on a specialized date index?

CREATE DATE INDEX date ON default(date)


var indexFormattedValue = function (val) {
var numericDateValue;
if (typeof val === “number”)
numericDateValue = val;
else
numericDateValue = Date.parse(val);
if (isNaN(numericDateValue)) {
return undefined;
}
return [256, dateToArray(new Date(numericDateValue))];
}

UPDATE: I updated function to support both numeric and string date parsing.

2

Jonathon,

Wouldn’t a numeric index serve the purpose?

CREATE INDEX date_index ON bucket_name(numeric_date_field)

SELECT * FROM bucket_name WHERE numeric_date_field >= TOMILLIS(some_date)

3

Though there is not a truly standard way to serialize dates with JSON, the general practice is to use ISO 8601. This is also how javascript automatically serializes Date objects. Forcing CB users to store dates as javascript timestamps (ie, unix timestamp * 1000), goes against the common trend and would involve a lot of people converting the date formats in their already saved documents.

Also, Elasticsearch indexes string dates, not numeric dates. So users would not be able to take advantage of both ES and N1QL because the two engines would have conflicting formats for dates stored in documents.
http://www.elasticsearch.org/guide/en/elasticsearch/reference/current/mapping-date-format.html

A special index would offer flexibility for the user. Javascript’s Date.Parse() supports many date string formats, and N1QL could also support numeric timestamps with date indexes, so users could choose what’s best for them and still be able to query dates efficiently.

An array based date index would offer flexibility for N1QL to further develop query abilities when it came to dates. You could do range scans at different levels (year, month, date, etc.) and potentially get aggregate data utilizing CB’s grouped reduce queries.

4

Thanks for the input. We’re already planning to support indexing of expressions (and not only fields). That, together with the right date functions, would be one way to accomplish what you are proposing. If that still doesn’t work, we’ll consider the special date index. We certainly want to support indexing and range scans on dates; the only question is how generic or specific is the solution.

Thanks.

5

If you are saying that …

CREATE INDEX date ON bucket_name(STR_TO_MILLIS(isoDateField))

… would be possible in the future, then that should be good enough.

A generic and more versatile solution is usually better.

6

+1 to above. Because JSON does not have a native date/time datatype, it makes sense to not have it as a native type in N1QL either. So relying on functions and user supplied expressions to parse is the best fit for N1QL.

7

Hi,

I have a query regarding the date functions in N1QL. Is it possible to get the current sysdate in a specific formate?
Like if i want sysdate to be returned in YYY-MM-DD format, how can i do that? Can anybody please reply? Also in the developer preview 4 they have mentioned plenty of date functions like CLOCK_STR([ fmt ]). But the format and example is not mentioned.

8

Hi,

For the format string, you can just provide a date in the format you want. For example:

SELECT CLOCK_STR(“2006-01-02”);

"results": [
    {
        "$1": "2015-03-24"
    }
],
9

Thank you for your quick response. Infact i was searching for the stuffs like “dd/mm/yyyy” which i am very much familiar. Anyhow its working. :slight_smile: . But the format is really confusing.