I have an SQLite table with three coloumns called day, start_time and end_time. I've added the times in the format of HH:MM and set the type to TEXT as per the SQLite recommendations for time storage. The problem comes when I try and search for times...
$stmt = $dbh->prepare('SELECT * FROM lesson WHERE day = :day AND start_time <= :time AND end_time >= :time');
$stmt->execute(array('day' => $day, 'time' => $time));
$result_arr = $stmt->fetch(PDO::FETCH_ASSOC);
return 'No lessons found';
With the above, it has no problem searching for the day only.. but when it comes to the time it has a problem. With the statement below return no results when it should have?
SELECT * FROM "lesson" WHERE day = 3 AND start_time <= 10:52 AND end_time >= 10:52
I've played around with direct SQL statements and was only able to get the select to return any results was to encapsulate the times in single quotes.
SELECT * FROM "lesson" WHERE day = 3 AND start_time <= '10:52' AND end_time >= '10:52'
I then tried to strval() the time but still can't get it to work. Is there a method of passing the time with the single quotes through in the statement, or am I doing something else wrong which should allow me to select without them. Maybe even a more efficient way?
I'm relatively new to databases and I've spent hours playing around with it. There's most probably an easy answer. I just don't have it... Any pointers would be greatly appreciated.
It's unusual to part date and time. Use single field ("moment"):
CREATE TABLE lesson( ... ,moment DATETIME );
DATETIME type means nothing to SQLite but is useful to the developer. Store time (+ date) in strings that follow this format:
YYYY-MM-DD YYYY-MM-DD HH:MM YYYY-MM-DD HH:MM:SS
Read more here, "Time strings".
Create index on that field to speed up queries:
CREATE INDEX IX_lesson_moment ON lesson(moment)
SELECT * FROM lesson WHERE moment >= '2013-10-30' AND moment < '2013-10-31' SELECT * FROM lesson WHERE moment >= '2013-10-30 10:00' AND moment < '2013-10-30 11:00'
You can also use parameters:
SELECT * FROM lesson WHERE moment >= ? AND moment <= ?
and pass time as a string value
For your application the type TEXT is wrong. Make two columns with hour and minute with INTEGER types, then you can fire up a select.