N1QL datetime where clause

SQL++
1

Hello, can i use clause where datetime > ‘2011-10-10’ in N1QL ??

Or I need to convert date in number like long.Parse(date.ToString(“yyyyMMddHHmmss”)); for do this?

Thanks.

2

Hi @hagen, the date comparison will work as a string comparison. you can use the TYPE() function to check the type being considered.
Here is the new Couchbase Server 4.0 Developer Preview Documentation if you have the latest couchbase server bits: http://docs.couchbase.com/4.0/n1ql/n1ql-language-reference/typecheckingfun.html

thanks
-cihan

3

In addition to what @cihangirb said, please note that if your dates are in ISO-8601 format, you can compare them directly as strings. We also have functions to convert date strings to/from UNIX epoch milliseconds (numbers).

4

Thank you very much. It’s clear now.

5

How would I compare to see if receivedDate >= currenDate where receivedDate = “2017-05-08T12:44:02.279968-04:00” and urrentDate.AddSeconds(-5)= 5/6/2017 11:20:51 AM.

6

WHERE receivedDate = “2017-05-08T12:44:02.279968-04:00” AND DATE_ADD_STR(currentDate, -5, ‘second’) = “2017-05-08T12:39:02.279968-04:00”

1 Like
7

Hi…

I have date format like this
"publishDate": “10/25/2017”,

Now, I have to check the condition where published date are in the last 90 days from current date.

I am using this format, but not getting any result.
select date_add_str(date_add_str(now_str(), ‘1111-11-11’), -1, “day”)

Please help…

8

You need to modify the date ISO 8601 format as described in https://developer.couchbase.com/documentation/server/current/n1ql/n1ql-language-reference/datefun.html

9

I am unable to understand your point. Please explain once.

Can we convert this “publishDate”: “10/25/2017” string format to date format.

10

N1QL provides convenience methods to allow you to manipulate and convert dates in string format. All date formats follow the ISO-8601 standard (YYYY-MM-DDThh:mm:ss.sTZD, ex: 2006-01-02T15:04:05.567+08:00).

“publishDate”: “10/25/2017” Doesn’t follow the ISO-8601 format, so N1QL date functions will not work.
You can store datetime as ISO-8601 format or change the datetime into ISO-8601 format on the fly using SUBSTR() and pass it through N1QL functions for manipulation.

Once you have input date in ISO-8601 format you can take look examples https://developer.couchbase.com/documentation/server/current/n1ql/n1ql-language-reference/datefun.html how to modify date.

Example: The following gives date 90 days before the current date or specified date.

SELECT DATE_ADD_STR(NOW_STR(),-90,"DAY");
SELECT DATE_FORMAT_STR(DATE_ADD_STR(NOW_STR(),-90,"DAY"),"1111-11-11");
SELECT DATE_ADD_STR("2016-01-01",-90,"DAY");
11

That means we have to store date like below:
“publishDate”: “2017-10-25”

12

If you want to use date functions or compare dates YES. Once date is in ISO-8601 format it is string comparable. Please read through link provided and it explain details.

13

Got your point… Thanks for the help…