Android sqlite time compare issue

Question

I am comparing time in android sqlite database, here is my table structure in the screen shots. Actually I have two times, like 8:00 and 7:30 (basically these are employee shift timings) in the string and I have a column c_TIME in my database 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 query 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'

the above query reruns 0 records – Achin Nov 30 '16 at 04:57 — Achin, Nov 30 '16 at 04:57

Tim Biegeleisen · Accepted Answer · 2016-11-30T05:21:42.407

0

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.

edited Nov 30 '16 at 05:21

answered Nov 30 '16 at 05:06

Tim Biegeleisen

502,043
27
286
360

this return 0 records – Achin Nov 30 '16 at 05:09
@Achin Are you certain that you even have any data which should be returned? The same table in your question has no matching data. – Tim Biegeleisen Nov 30 '16 at 05:10
yes i have 3 values in c_date 2016-11-30 09:18:19 , 2016-11-25 09:18:19 and 2016-11-26 06:30:00 and this lies b/w '07:30:00' and '08:00:00' – Achin Nov 30 '16 at 05:18
No, they don't. `06:30` is _earlier_ than your desired range, and the 9 am times are _later_ than this range. Please check your logic. – Tim Biegeleisen Nov 30 '16 at 05:21
actually , i think i have save my time in 12 hr format , is your query work with 24 hr format time? – Achin Nov 30 '16 at 05:24
Yes, `%H` should be for 24 hour time. – Tim Biegeleisen Nov 30 '16 at 05:24
ok , let me check my logic again ,one thing i want to ask is that should i store my datetime in 24hr format ? – Achin Nov 30 '16 at 05:25
@Achin If you _don't_ store your datetimes with 24 hour resolution, then you would have to store AM/PM of some sort, which seems ugly to me. So better go with military time. – Tim Biegeleisen Nov 30 '16 at 05:28
ok that is fine but if i want to get only last 7 days records in the above query than can i use (SELECT DATE('now', '-7 day')) ? – Achin Nov 30 '16 at 05:39
@Achin This is fairly different requirement from your original question. You might want to consider opening a new one. – Tim Biegeleisen Nov 30 '16 at 05:41
@Achin Look here: http://stackoverflow.com/questions/3634542/sqlite-select-for-last-7-days – Tim Biegeleisen Nov 30 '16 at 05:43

score 0 · Answer 2 · answered Nov 30 '16 at 05:07

Try:

select * from ATTENDANCE where c_date between 'yyyy-mm-dd' and 'yyyy-mm-dd';

Instead of using c_TIME,use c_date field.

You can change the date format in the query accordingly to retrieve data between two specific times.

Sqlite supports following formats:

YYYY-MM-DD

YYYY-MM-DD HH:MM

YYYY-MM-DD HH:MM:SS

YYYY-MM-DD HH:MM:SS.SSS

YYYY-MM-DDTHH:MM

YYYY-MM-DDTHH:MM:SS

YYYY-MM-DDTHH:MM:SS.SSS

HH:MM

HH:MM:SS

HH:MM:SS.SSS

now

DDDDDDDDDD

See here

Android sqlite time compare issue

2 Answers2