检查sqlite的一个特定的时间间隔内时间 [英] Check time within a particular time interval in sqlite
问题描述
您好我需要在一个特定的时间间隔内,我的Android应用程序检查时间。时报存储在sqlite的DB。
我怎样才能让选择对哪些检查,如果用户选择的时间间隔是不冲突的,里面DB?
时间间隔例如,如果从6 00 18 00用户选择的时间,然后他应该无法时间之间添加另一个时间间隔6 00 18 00。如果用户取件时间9 00则其内部06 00 - 18 00这时候已经在数据库中。任何想法我怎么能做到这一点。
这是我的选择,但无法工作好。
SELECT EXISTS(SELECT 1 FROM nepritomnosti
WHERE ID ='821'
和dBeginDate ='2014年8月13日
AND((的strftime('%s'的,tBeginTime)LT; =的strftime('%s'的,'17:00'))
AND(的strftime('%s'的,tEndTime)GT; =的strftime('%s'的,'21:00')))
LIMIT 1)
这是不正确的公式检查两个时间间隔是否重叠。
您需要交换其他区间的两个端点;如果他们被称为 TMIN
/ TMAX
:
tBeginTime< =最高气温,tEndTime> = TMIN
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)
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
这篇关于检查sqlite的一个特定的时间间隔内时间的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!