Nice work. Another thing we want to introduce is conversion between various time zones. As you know, the table
route provides the departure and arrival times in Central European Time. Let's see how we can show the arrival time in the arrival airport time zone for a flight from Madrid to Tokyo:
SELECT arrival AT TIME ZONE 'Asia/Tokyo'
WHERE from_airport = 'MAD'
AND to_airport = 'NRT';
As you can see, we need to write
AT TIME ZONE followed by the timezone in apostrophes. In our database, we usually provide the time zone in the format Continent/City, but you need to watch out, because some databases have other conventions.
MySQL uses the function
convert_tz to convert timestamps between various timezones.