CODEWITHSUNDEEP
androidsqlitedatetimeoverlapnstimeinterval
pavol.franek
pavol.franek

Reputation: 1416

Check time within a particular time interval in sqlite

Hi I need in my android app check time within a particular time interval. Times are stored in SQlite DB.

How I can make select which check if time interval selected by user is not in conflict with time interval inside DB?

For example if user select time from 6 00 to 18 00 then he should not able add another time interval between time 6 00 to 18 00 . If user pickup time 9 00 then its inside 06 00 - 18 00 and this time is already in DB. Any idea how I can do this.

This is my select but not work good. 

SELECT EXISTS(SELECT 1 FROM nepritomnosti
              WHERE id = '821'
                AND dBeginDate = '2014-08-13'
                AND ((strftime( '%s', tBeginTime ) <= strftime( '%s', '17:00' ))
                AND  (strftime( '%s', tEndTime )   >= strftime( '%s', '21:00' )))
              LIMIT 1)

Upvotes: 1

Views: 343

Answers (1)

CL.
CL.

Reputation: 180162

This is not the correct formula to check whether two intervals overlap.

You need to exchange the two endpoints of the other interval; if they are called tMin/tMax:

tBeginTime <= tMax AND tEndTime >= tMin

Upvotes: 2

Related Questions