How to do date conditions for sugar ORM queries

Question

Been stuck trying to make this work for a while: I am using sugar ORM for sqlite in my android app and I am trying to write a query with a condition on a date field. The where clause format for sqlite dates does not seem to be working.

eg:

MySugarRecordClass.find(MySugarRecordClass.class, "some_date_field>?", "2015-01-01")

or

MySugarRecordClass.find(MySugarRecordClass.class, "date(some_date_field)>date(?)", "2015-01-01")

I have tried using several date formats but I still can't get it.

Answer 1

Unfortunetly SQLite (the DB behind Sugar) does not have a date type: http://www.sqlite.org/datatype3.html. I personally store and compare my dates as integers after converting them to the UNIXTIMESTAMP.

You can also store the date as a string to do a comparison on, but I suggest sticking with the 'YYYY-mm-dd hh:mm:ss' format in 24 hour format, like so: '2015-08-11 13:13:00' (time for 1:13pm exactly).

Whatever way you do it, you need to be consistent and save/compare your dates that exact way every time.