当前位置: 动力学知识库 > 问答 > 编程问答 >

sql - SQLite and time searching issues

问题描述:

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...

 try {

$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);

if(empty($result_arr)) {

return 'No lessons found';

}

else {

return $result_arr;

}

}

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 records:

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.

分享给朋友:
您可能感兴趣的文章:
随机阅读: