Time Comparison not working

SQL++
1

SELECT “2019-10-01 14.45.12” <= DATE_ADD_STR(CLOCK_TZ(‘GMT’, ‘1111-11-11 11.11.11’), 5, “minute”),CLOCK_TZ(‘GMT’, ‘1111-11-11 11.11.11’) as GMT,
DATE_ADD_STR(CLOCK_TZ(‘GMT’, ‘1111-11-11 11.11.11’), 5, “minute”) as WITHFIVE

[
{
“$1”: true,
“GMT”: “2019-10-01T14:31:55.09Z”,
“WITHFIVE”: “2019-10-01T14:36:55.09Z”
}
]

why it returned true when the given time is a future date comparing to the current time ?? am i doing anything wrong

2

Date String not following ISO-8601 format. Date Functions | Couchbase Docs

In your case between date and time there is space. Once date is same it compares space with T which always less.

Once date follows ISO-8601 format it is string comparable.

  • T is missing between date and time (not a space)

  • : is needed between hours vs minutes vs seconds (not a dot)

     SELECT "2019-10-01T16:45:12" <= DATE_ADD_STR(CLOCK_TZ("GMT", "1111-11-11T11:11:11"), 5, "minute"),
                        CLOCK_TZ("GMT", "1111-11-11T11:11:11") as GMT,
 DATE_ADD_STR(CLOCK_TZ("GMT", "1111-11-11T11:11:11"), 5, "minute") as WITHFIVE
3

You are right and it worked without “T” . Let me know if anything wrong with this

SELECT “2019-10-01 16:45:12” <= DATE_ADD_STR(CLOCK_TZ(“GMT”, “1111-11-11 11:11:11”), 5, “minute”),
CLOCK_TZ(“GMT”, “1111-11-11 11:11:11”) as GMT,
DATE_ADD_STR(CLOCK_TZ(“GMT”, “1111-11-11 11:11:11”), 5, “minute”) as WITHFIVE

4

Table 3 https://docs.couchbase.com/server/current/n1ql/n1ql-language-reference/datefun.html
Lists formatted strings if you follow consistently on both sides of comparsion it will be fine.