Re: SQL lite query for get the records in which current time range exist between range of time slots
Date: Fri, 29 Apr 2016 20:30:57 +0200
Message-ID: <ng092i$b3b$1_at_dont-email.me>
On 04/29/2016 02:59 PM, smallubotlla_at_gmail.com wrote:
> The requirement is to get the time slot from sql table with the help of current time . Here is the table below . The problem arises when i am trying to get the time slot that surpasses 24th hours for example like the Night shift . if my current time is 21:00 i am unable to get the time slot . query made so far
>
>
> SELECT
> *,CURRENT_TIME
> FROM
> tbl_shift
> WHERE CURRENT_TIME BETWEEN shift_start
> AND shift_end
>
>
>
>
>
> shift_id shift_name shift_start shift_end
> -------- ---------- ----------- -----------
> 1 Morning 09:00:00 13:00:00
> 2 Evening 13:00:00 16:15:00
> 3 Night 16:01:00 09:00:00
>
You should probably post in an sqllite newsgroup to get better answers, but something like the following should work (assuming shift_start etc is of type TIME):
select SHIFT_START, SHIFT_END
FROM TBL_SHIFT
WHERE TIME('16:05:00') BETWEEN SHIFT_START
AND CASE WHEN SHIFT_END < SHIFT_START THEN TIME('16:05:00') ELSE SHIFT_END END;
Replace both occurrences of TIME('16:05:00') with NOW() or similar
/Lennart Received on Fri Apr 29 2016 - 20:30:57 CEST