Andrew De Forest Andrew De Forest - 5 months ago 7
SQL Question

Get records after a certain time in PostgreSQL

I have a table that looks like this:

id | flight_number | departure_time | arrival_time
---+---------------+----------------+-------------
1 | UAL123 | 07:00:00 | 08:30:00
---+---------------+----------------+-------------
2 | AAL456 | 07:30:00 | 08:40:00
---+---------------+----------------+-------------
3 | SWA789 | 07:45:00 | 09:10:00


I'm trying to figure out an SQL query that can get upcoming flights based on departure time given the current time. For instance, at
07:20
, I would like to return
AAL456, SWA789
since those flights have not departed yet. At
07:40
, I would like to just return
SWA789
. What is a good way to do this?

Answer

Well, you can use LOCALTIME to get the current time. So, if the departure_time is stored as a time, then:

select t.*
from t
where t.departure_time > localtime;

This assumes no time zone information is part of the time value. Also, it will return no flights after the last flight has departed for a day (which is consistent with the phrasing of your question).

Comments