Select record conditionally by comparing date and time

user_4131060 0
Viewed 39

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?

Result of SELECT

select sql-order-by date sqlite
2 Answers

For the condition:

current time is between record start and stop times

you need this boolean expression in the ORDER BY clause:

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

and for the condition:

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

you need this:

julianday('now', 'localtime') > session_stop

Write your query like this:

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

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

user_17067440
Related