Select record conditionally by comparing date and time-CodePudding

Using SQLite, how do I retrieve the first record where current time is between record start and stop times, OR ELSE, if current time is outside start/stop times of all existing records, instead retrieve the record that comes next in time after current local time?

Here's my table:

TABLE sessions (
    session_id    INTEGER PRIMARY KEY AUTOINCREMENT,
    session_start REAL,
    session_stop  REAL,
    group_id      INTEGER REFERENCES [groups] (group_id) ON DELETE CASCADE
);

Example data:

session_id  session_start       session_stop        group_id
1           2459599.755438762   2459599.963772095   1
2           2459600.0471054283  2459600.1721054283  2
3           2459600.755438762   2459600.963772095   1

The session table has a foreign key, group_id, but for sake of the example maybe we can leave it aside.

EDIT:

Tried mankowitz suggestion within a small test application:

SQL.Text:='SELECT *, julianday(''Now'',''localtime'') AS julian_now FROM sessions '
 'WHERE session_start > julian_now '
 'ORDER BY session_stop < julian_now LIMIT 1';

Current time at runtime was 2022-01-17 05:40:45 and the record with session_id 16 should have been selected since it is the closest to in time. How to modify the code for this?

CodePudding user response：

select * from sessions where sesson_start>{currenttime} order by session_end<{currenttime} limit 1

CodePudding user response：

For the condition:

current time is between record start and stop times

you need this boolean expression:

julianday('now', 'localtime') BETWEEN session_start AND session_stop

in the ORDER BY clause.

SELECT *
FROM sessions 
ORDER BY julianday('now', 'localtime') BETWEEN session_start AND session_stop DESC,
         session_start  
LIMIT 1;