Achin Achin - 1 year ago 62
Android Question

Android sqlite time compare issue

I am comparing time in android sqlite database , here is my table strcutre in the screen shots,
actually i have two time like 8:00 and 7:30 (basically these are emplyee shift timings) in the string and
i have colum c_TIME in my db and i want all the records who lies in b/w my two time i.e.(8:00 and 7:30) from c_TIME.
i have used this quey but it is not working , please anyone guide me what i am doing wrong

WHERE strftime('%H','08:00') > strftime('%H', c_TIME) AND
time('08:00') < strftime('%H', c_TIME) AND

enter image description here

Answer Source

The following should work:

WHERE strftime('%H:%M:%S', c_date) > '07:30:00' AND
      strftime('%H:%M:%S', c_date) < '08:00:00' AND
      STATE = 'IN'


Instead of using the c_TIME field, you should use c_date. Actually, I don't see any value in keeping around a column which stores the time separately.

strftime returns a string based on some sort of datetime/timestamp input. The output from strftime('%H:%M:%S', c_date) should have the form HH:MM:SS, and this string can be compared lexicographically against 08:00:00 and 07:30:00. The comparison should still work, because for strings of the same length it should still sort numerically.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download