SQLite: Select row based on time of day?

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:

If CurrentTime is 08:30 it 开发者_如何学Cwill return zone1 If CurrentTime is 16:40 it will return zone2 If Currenttime is 04:01 it will return zone4

and so on...

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.

Thanks!

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;

Have you tried a having clause?

SELECT * FROM table WHERE StartTime >= time('now', 'localtime')
HAVING StartTime = MIN(StartTime) 

Two versions.

This is the easiest to understand, but it assumes that max(Name) is meaningful. That is, it assumes that the values in "Name" are ordered from low to high.

SELECT max(Name) 
FROM yourtable
WHERE StartTime< time("16:40:00");

This version uses a scalar subquery. It doesn't depend on any meaningful order to the names. I'd consider this version to be more robust.

SELECT Name 
FROM yourtable 
WHERE StartTime = (SELECT max(StartTime) 
                   FROM yourtable
                   WHERE StartTime < time("16:40:00")
);

• You'll want to replace the time() literals with time('now','localtime') in production.
• Expect an empty set if you run any of these queries before 08:00 local time.