Problem select date from string (yyyy-mm-dd hh:MM:ss)

SQL++
,
1

Hi all,

i have document like this:
{
“mu”: {
“id”: “test:1”,
“dt”: “2017-07-07 07:00:00”,
“type”: “test”
}
},
{
“mu”: {
“id”: “test:2”,
“dt”: “2017-07-07 12:00:00”,
“type”: “test”
}
},
{
“mu”: {
“id”: “test:3”,
“dt”: “2017-07-05 09:00:00”,
“type”: “test”
}
},
{
“mu”: {
“id”: “test:4”,
“dt”: “2017-07-04 14:00:00”,
“type”: “test”
}
},
{
“mu”: {
“id”: “test:5”,
“tgl”: “2017-07-01 07:00:00”,
“type”: “test”
}
}

right now i want to select document that dt is greater from 2017-07-04, so i can have 3 document in the result.
i already try:

select * from mu where type=‘test’ and STR_TO_MILLIS(dt) > STR_TO_MILLIS(‘2017-07-04 00:00:00’)

but the syntax problem. please help. thanks

best regards,

2

What errors it gives. It is works.

 insert into default values("test1",{"id": "test:1", "dt": "2017-07-07 07:00:00", "type": "test" });
select * from default where type='test' and STR_TO_MILLIS(dt) > STR_TO_MILLIS('2017-07-04 00:00:00');
{
    "requestID": "39dc0c43-ba12-4fba-89f8-95455d9d7fb2",
    "signature": {
        "*": "*"
    },
    "results": [
        {
            "default": {
                "dt": "2017-07-07 07:00:00",
                "id": "test:1",
                "type": "test"
            }
        }
    ]
}

If date is ISO 8601 format it is string comparable.

select * from mu where type=‘test’ and dt > ‘2017-07-04’;

Your query returns 4 documents not 3 (If the date string does not explicitly declare the value of a component, then a value of 0 is assumed. For example 2016-02-07 is equivalent to 2016-02-07T00:00:00. This is with the exception of the time zone, which if unspecified in the date string will default to the local system time zone.). Couchbase SDKs