CODEWITHSUNDEEP
sqlsqlite
youngminz
youngminz

Reputation: 1434

Add a new column based on the current time

I'm using sqlite3 with Python. I would like to select the closest time from the current time while listing the classrooms. This is [timetable] database.

     [ timetable ]
lecture_room start_time
---------------------
A211         13:00
A211         14:00
B107         13:00

If I query when the current time is 11:34, the expected results are as follows. (I would like one classroom to be queried only once.)

lecture_room next_time
---------------------
A211         13:00
B107         13:00

If I query when the current time is 13:34, the expected results are as follows. 

lecture_room next_time
---------------------
A211         14:00
B107         <null>

This is my SQL Query that I coded, but this SQL query makes error ([1] [SQLITE_ERROR] SQL error or missing database (no such column: L))

SELECT lecture_room as L,
  (SELECT MIN(start_time)
   FROM timetable
   WHERE
     time("now", "localtime") <= time(start_time)
    AND
       L = lecture_room
  )
FROM timetable;

Upvotes: 0

Views: 38

Answers (1)

CL.
CL.

Reputation: 180040

To get only one entry for each room, use DISTINCT (in a subquery, otherwise it would apply to both columns).

The correlated subquery is almost correct; you have to refer to the correct table:

SELECT lecture_room,
       (SELECT min(start_time)
        FROM timetable AS t2
        WHERE t2.lecture_room = t1.lecture_room
          AND t2.start_time >= time('now', 'localtime')
       ) AS next_time
FROM (SELECT DISTINCT lecture_room
      FROM timetable) AS t1;

Upvotes: 1

Related Questions