Summer Deals - hours only!Up to 80% off on all courses and bundles.-Close
Introduction
Dates
Time
Timestamps
Extract functions
Timezone conversion
26. AT TIME ZONE
Intervals
Current date and time
Review

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 the function convert_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). Name the last column local_time.

Stuck? Here's a hint!

Use AT TIME ZONE 'Europe/Warsaw'.