-2

I am having trouble in selecting data from sqlite.

I have stored some tv shows in db, there are 2 columns in my db, show 'start_time' and 'end_time' now i want to select data based on a time slot like if current time is 2 pm than time slot will be 2:00 pm to 2:30 pm, I am able to get shows that are playing in between 2 slots. But between query will not work for the shows that are already started like a show whose start_time is 1 pm and end_time is 3 pm.

EDIT:

I am developing an android app, in which i have to store tv shows for 3 days in sqlite db. Once all data stored in db, i have to show data according to current time slot in listview, time slot is of half hour e.g 1:00 to 1:30 , 1:30 to 2:00, 2:00 to 2:30 etc. I want to show all shows currently playing in the given time slot.

I tried using sqlite between query

SELECT * FROM table_name WHERE start_time BETWEEN 'time_stamp' and 'time_stamp1'

time_stamp is first time of time slot i.e 1:00 from above example

time_stamp2 is second time of time slot i.e 1:30 from above example

This will give me all shows that will start and end in between given time slot.

But if show starts at 12:00 and will end after 2:00 or 3:00 between query will not work in that case

I have also try

SELECT * FROM table_name WHERE start_time<='time_stamp' and end_time>='time_stamp1'.

Vinay Bisht
  • 1
  • 1

2 Answers2

1

If I understand your question right, generic way of finding such data is filter by show.end_time >= :param_start_time and show.start_time <= :param_end_time.

onestep.ua
  • 118
  • 7
0

here is the query will work to get all shows which are between 02:00:00 PM to 02:30:00 PM

SELECT start_time, end_time FROM table-name where (start_time < '02:30:00' and (end_time > '02:00:00' or end_time > '02:30:00'));

(show.start_time < param_end_time and (show.end_time > param_start_time or show.end_time > param_end_time))

and get following results :

start_time end_time
02:00:00 03:00:00
02:00:00 02:30:10
01:00:00 03:00:00
02:29:59 03:00:00
02:18:00 02:45:00
01:30:00 02:15:00
02:10:00 02:20:10
Nitin Kinger
  • 31
  • 2
  • I wonder how this answer is better than onestep one, `(end_time > '02:00:00' or end_time > '02:30:00')` is redundant so could be simplified into `start_time <= '02:00:00' and end_time > '02:00:00'` which is pretty much onestep's answer. – Cleptus Jul 25 '18 at 12:14
  • Hi, this is your one step query "start_time <= '02:00:00' and end_time > '02:00:00'" will not get shows which start on 02:10:00 – Nitin Kinger Jul 25 '18 at 12:37
  • answer by onestep is not correct "show.end_time >= :param_start_time and show.start_time <= :param_end_time" it will get records with end time 02:00:00 PM also records with start time : 02:30:00 – Nitin Kinger Jul 25 '18 at 13:01
  • The OP states that current time is 02:00 so not getting shows that start on 02:10 should be correct if I got the question right. – Cleptus Jul 25 '18 at 13:54