Query seems to work only when there is a big gap between comparing dates

[taras-nikulin (Тарас Никулин)]

In my app i query data from server by comparing created value on server with the value saved on the device.
When whereClause looks like this: “05/10/2016 16:20:00 > 04/04/2016 00:00:00”, then everything is okay.
If it looks like this: “05/10/2016 16:20:00 > 05/10/2016 16:00:00” it returns nothing.
Thanks in advance!

let operation = OperationsBackendless()

        print(lastUpdateOperations)

        let whereClause = "((companyId = '\(companyIdentifier!)' AND created > '\(lastUpdateOperations!)' AND deleted = false) OR (updated > '\(lastUpdateOperations!)' AND deleted = true))"

        let dataQuery = BackendlessDataQuery()

        dataQuery.whereClause = whereClause

        

        print(whereClause)

        

        let startTime = NSDate()

        

        backendless.persistenceService.of(operation.ofClass()).find(

            dataQuery,

            response: { ( operations : BackendlessCollection!) -> () in

                let currentPage = operations.getCurrentPage()

                print("Loaded \(currentPage.count) objects")




                for operaton in currentPage as! [OperationsBackendless] {

                    print("operation name = \(operaton.documentName)")

                }

                updateDefaultsTime()

                

                print("Total time (ms) - \(1000*NSDate().timeIntervalSinceDate(startTime))")

            },

            error: { ( fault : Fault!) -> () in

                print("Server reported an error: \(fault)")

            }

        )

[mark-piller]

When whereClause looks like this: "05/10/2016 16:20:00 > 04/04/2016 00:00:00", then everything is okay

but what does “05/10/2016 16:20:00 > 04/04/2016 00:00:00” even mean??

[taras-nikulin (Тарас Никулин)]

it is an example. i compare two dates like in the documents (look at my code).

Documents says, that i may compare dates like this:
updated > ‘23-Mar-2015’
And one of the supporttings formats is MM/dd/yyyy HH:mm:ss

[mark-piller]

Yes, that is correct. A query should reference a column name and a specific value. Could you try testing your query using console? Here’s an article describing how to do that:

https://backendless.com/feature-14-sql-based-search-for-data-objects-using-console/

[taras-nikulin (Тарас Никулин)]

I attached 3 files. On the first image you can find part of my database without SQL search. On the second image SQL search where timestamps are very close. And the third, where there is more than 1 hour gap (I also tried 21:00,20:00 and so on. Works with 19:00 and lower).

[mark-piller]

I just checked and noticed that the date/time comparison operators work reliably if the date is specified as the number of milliseconds since epoch. Try to use this site to convert your date/time to milliseconds and then use the milliseconds value in the query (without quotes): http://currentmillis.com/

[taras-nikulin (Тарас Никулин)]

okay, now it works in SQL search, thank you)