SQLite:根据一天中的时间选择行? [英] SQLite: Select row based on time of day?

问题描述

我有一个 SQLite 表，如下所示:

I have a SQLite table like the following:

+-----------+-------+
| StartTime | Name  |
+-----------+-------+
|  08:00:00 | zone1 |
|  13:00:00 | zone2 |
|  17:30:00 | zone3 |
|  22:00:00 | zone4 |
+-----------+-------+

我正在尝试编写一个将根据当前时间返回行的查询:

I'm trying to write a query that will return the row based on the current time:

如果 CurrentTime 是 08:30，它将返回 zone1如果 CurrentTime 是 16:40 它将返回 zone2如果当前时间是 04:01 它将返回 zone4

If CurrentTime is 08:30 it will return zone1 If CurrentTime is 16:40 it will return zone2 If Currenttime is 04:01 it will return zone4

等等...

到目前为止，我有一些运气，但不是我想要的

So far I had some luck but not exactly what I wanted

SELECT * FROM table WHERE StartTime >= time('now', 'localtime') 
ORDER BY StartTime LIMIT 1;

我尝试了上述语句的一些变体，但都没有返回我想要的结果.

I've tried some variations of the above statement, but none returns the result I'm after.

谢谢！

推荐答案

如果您还添加一个EndTime"字段，您的生活就会轻松很多，因为您可以简单地检查当前时间是否在开始和结束时间.

You'll make your life a lot easier if you add an "EndTime" field as well, as you can then simply check if the current time is within the start and end time.

例如，如果您的数据库表包含以下内容...

For example, if your database table consisted of the following...

+-----------+----------+-------+
| StartTime | EndTime  | Name  |
+-----------+----------+-------+
|  08:00:00 | 12:59:59 | zone1 |
|  13:00:00 | 17:29:59 | zone2 |
|  17:30:00 | 21:59:59 | zone3 |
|  22:00:00 | 07:59:59 | zone4 |
+-----------+----------+-------+

...您可以简单地使用以下方式的查询:

...you could simply use a query along the lines of:

SELECT Name FROM table WHERE StartTime >= time('now', 'localtime')
AND EndTime <= time('now', 'localtime')
ORDER BY StartTime LIMIT 1;

这篇关于SQLite:根据一天中的时间选择行?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！