Deals Of The Week - hours only!Up to 80% off on all courses and bundles.-Close

Wrap course as a gift

Prepare your gift and double-check it looks just right.

Form validation errors

Recipient's Name:
Recipient's Email:
Your Message (Optional):
Proceed to Checkout

Common Functions in PostgreSQL

Introduction
Dealing with dates
Working with time
Date and time data types in PostgreSQL
Extracting dates and times
Timezone conversion
40. AT TIME ZONE
Format date and time
Current date and time data
Summary

Instruction

Nice work! Another thing we want to introduce is the conversion between various time zones. As you know, the route table provides the departure and arrival times in Central European Time. Let's see how we can show the same time in different timezones:

SELECT
  launched_timestamp AS original,
  launched_timestamp AT TIME ZONE 'Asia/Tokyo' AS tokyo
FROM aircraft;

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 PostgreSQL has other conventions.

Exercise

For each aircraft that was withdrawn, show the model, and the same time in two time zones:

  • local time of Paris (Europe/Paris) – as the paris_time column.
  • Pacific Standard Time (PST) – as the pst column.

Stuck? Here's a hint!

Use this expression:

  withdrawn_timestamp AT TIME ZONE 'Europe/Paris' AS paris_time,
  withdrawn_timestamp AT TIME ZONE 'PST' AS pst