Achin Achin - 5 days ago 5
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

SELECT *
FROM ATTENDANCE
WHERE strftime('%H','08:00') > strftime('%H', c_TIME) AND
time('08:00') < strftime('%H', c_TIME) AND
STATE = 'IN'


enter image description here

Answer

The following should work:

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

Comments:

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.

Comments