How the timezone() Function Works in PostgreSQL

Posted on by Ian

In PostgreSQL, you can use the timezone() function to convert a timestamp to another timezone.

Syntax

The syntax goes like this:

timezone(zone, timestamp)

Where zone is the timezone that you want the timestamp argument to be converted to.

The result you get will depend on whether the original timestamp value includes a timezone or not (and what that value is).

Example 1 -When the Original Timestamp DOES Include the Timezone

Here’s what happens when the original timestamp includes the timezone.

SELECT timezone('Indian/Mauritius', timestamp with time zone '2020-10-25 00:00:00+00');

Result:

2020-10-25 04:00:00

When the original timestamp includes the timezone, the timezone() function shifts the original timestamp value to the specified time zone, and returns the value without a timezone.

Here’s what happens if I change the timezone of the input timestamp.

SELECT timezone('Indian/Mauritius', timestamp with time zone '2020-10-25 00:00:00+01');

Result:

2020-10-25 03:00:00

The resulting timestamp is shifted according to the input timezone.

And if you specify timestamp with time zone, but the original timestamp doesn’t actually include a timezone, then the original timestamp value is shifted to the local timezone.

SELECT timezone('Indian/Mauritius', timestamp with time zone '2020-10-25 00:00:00');

Result:

2020-10-24 18:00:00

In this case, the resulting timestamp has been put back six hours. This means that my local timezone is six hours ahead of Indian/Mauritius.

We can see the timezone offset that was used by selecting the input value directly.

SELECT timestamp with time zone '2020-10-25 00:00:00';

Result:

2020-10-25 00:00:00+10

Example 2 – When the Original Timestamp DOESN’T Include the Timezone

Here’s what happens when the original timestamp doesn’t include a timezone.

SELECT timezone('Indian/Mauritius', timestamp without time zone '2020-10-25 00:00:00');

Result:

2020-10-25 06:00:00+10

When the original timestamp doesn’t include a timezone, the result is displayed using the current TimeZone setting and the timezone offset is appended.

This applies any time you specify timestamp without time zone, even if the timestamp does in fact contain a timezone offset.

SELECT timezone('Indian/Mauritius', timestamp without time zone '2020-10-25 00:00:00+12');

Result:

2020-10-25 06:00:00+10

This is to be expected, because if I select just the timestamp without time zone value, this is what I get:

SELECT timestamp without time zone '2020-10-25 00:00:00+12';

Result:

2020-10-25 00:00:00

Example 3 – localtimestamp

Let’s use the localtimestamp function to run a comparison between the current timestamp in my own timezone, and the resulting timestamp after using the timezone() function to convert it to a different timezone.

\x
SELECT 
  localtimestamp,
  timezone('Indian/Mauritius', localtimestamp);

Result:

localtimestamp | 2020-07-08 15:42:04.965221
timezone       | 2020-07-08 21:42:04.965221+10

The localtimestamp function returns a timestamp data type, which comes with an implicit “without time zone”. In other words, timestamp and timestamp without time zone are the same thing.

As demonstrated previously, when no timezone is specified in the original timestamp, the current TimeZone setting is used and appended to the result. And so that’s what we get when using localtimestamp.

By the way, in this example I used \x to display the result using expanded display/vertical output, just to make it easier to read.

Example 4 – current_timestamp

Now let’s use the current_timestamp function instead of localtimestamp.

SELECT 
  current_timestamp,
  timezone('Indian/Mauritius', current_timestamp);

Result (using vertical output):

current_timestamp | 2020-07-08 15:42:04.335669+10
timezone          | 2020-07-08 09:42:04.335669

This time the timezone offset has been appended to the original timestamp, and the timezone() result no longer includes it.

We get this result because the current_timestamp function returns a timestamp with time zone data type.

Example 5 – Using time Values

The timezone() function also works with time values (time with time zone and time without time zone).

However, when using it on time with time zone values, the timezone offset is appended to the result.

SELECT 
  timezone('Indian/Mauritius', time with time zone '00:00:00+00'),
  timezone('Indian/Mauritius', time with time zone '00:00:00+01'),
  timezone('Indian/Mauritius', time with time zone '00:00:00');

Result (using vertical output):

timezone | 04:00:00+04
timezone | 03:00:00+04
timezone | 18:00:00+04

And for completeness, here are those same values using a time without time zone data type.

SELECT 
  timezone('Indian/Mauritius', time without time zone '00:00:00+00'),
  timezone('Indian/Mauritius', time without time zone '00:00:00+01'),
  timezone('Indian/Mauritius', time without time zone '00:00:00');

Result (using vertical output):

timezone | 18:00:00+04
timezone | 18:00:00+04
timezone | 18:00:00+04

Check the Local Timezone

If you want to check your own local timezone, run SHOW TIMEZONE.

Here’s what I get when I run that command.

SHOW TIMEZONE;

Result:

Australia/Brisbane

Check the Timezone Offset

Armed with the above knowledge, I can now check the two timezone offsets by querying the pg_timezone_names view.

SELECT * 
FROM pg_timezone_names
WHERE name = 'Indian/Mauritius'
OR name = 'Australia/Brisbane';

Result (using vertical output):

name                | abbrev | utc_offset | is_dst
--------------------+--------+------------+--------
Indian/Mauritius    | +04    | 04:00:00   | f
Australia/Brisbane  | AEST   | 10:00:00   | f

See Return a List of Timezones Supported by PostgreSQL for more options and examples for returning the timezone in Postgres.