Introduction
Dates
Time
Timestamps
Extract functions
Timezone conversion
26. AT TIME ZONE
Intervals
Current date and time
Revision

Instruction

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'
FROM route
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 function conver_tz to convert timestamps between various timezones.

Exercise

For the route from Keflavik (KEF) to Gdansk (GDN), show the departure time from Keflavik in local time for Gdansk (Europe/Warsaw).

Stuck? Here's a hint!

Use AT TIME ZONE 'Europe/Warsaw'.

Console

Code editor

Result

TableConsole