1. Working with time zone information
In this video, we will learn how to work with time zone information.
2. Time zone data
The TIMESTAMP data type stores a date and time without a time zone. This can suffice when using a single time zone. This approach is also well suited to the situation where all timestamps are already in a global time standard, or UTC.
Suppose we want to store several different time zones. We should use the data type TIMESTAMPTZ which also includes time zone data.
3. Time zone names
Here's a quick tip. We may not know all of the time zones or their abbreviations.
PostgreSQL has a handy guide in pg_timezone_names. We can query it as we would a normal table and get a list of names, abbreviations, and the UTC offset. The is_dst field indicates whether that location is currently observing daylight savings.
4. Show the time zone
We can confirm our time zone setting by calling SHOW TIMEZONE. Ours is Europe/Brussels.
5. Time zone data in tables
Suppose we have a table called datetimes with two fields called datetime and datetimetz. The datetime field uses the TIMESTAMP data type. The datetimetz field uses the TIMESTAMPTZ data type.
Let’s insert a row into our table using a timestamp without time zone information into both fields. The new row of data would appear as shown below the query. Because the time zone information was not provided to the datetimetz field, it defaults to the DataCamp server time zone, which is Central European Time.
6. Verify the data type
Let’s prove that each field contains the expected data type. We can do this with the pg_typeof function where the input is the field name.
Here we see the data types are as expected.
7. Using time zone information
Let’s repeat, this time including time zone information. We’ll use a UTC offset of zero, indicated by the plus-zero-zero appended to the time string.
The datetime field ignores the time zone offset information. It stores the same time as it did in the previous query.
But now we see a difference in the datetimetz field. It converted the timestamp to UTC, and then stored the converted timestamp value in the table. This corresponds to the UTC offset between our local time setting and UTC time. Twelve o'clock UTC time, is one o'clock in Central Europe.
8. Adding time zone information
We can use AT TIME ZONE if we need to add or change a time zone.
Here’s an example of adding a time zone to a timestamp without time zone.
With AT TIME ZONE, we are specifying that the time zone should be Europe/London. This has changed the type from TIMESTAMP to TIMESTAMPTZ.
This query interprets the timestamp, creates a TIMESTAMPTZ type, and displays that in the default time zone. In our case, Brussels.
9. Changing and removing time zones
Here’s an example of changing a timestamp with a time zone to a timestamp. The code is almost identical, only this time, instead of adding a time zone, we are changing it to the one we want.
The original time zone is GMT, or UTC, as shown by the plus zero zero offset.
The query makes this shift, and removes the time zone designation as the time is now in the time zone we want it to be, Europe/Paris, which is one hour ahead of the time in the query. As we can see, the offset is no longer shown in the output.
10. Let's practice!
Time for practice!