Three syntax contexts that use the specification of a UTC offset

You can specify the UTC offset, either directly as an interval value or indirectly as a timezone name in three different syntax contexts:

Specify the UTC offset using the session environment parameter 'TimeZone'

The session environment parameter TimeZone is defined by a precedence scheme,

  • It can be set in the file postgresql.conf, or in any of the other standard ways described in Chapter 19. Server Configuration of the PostgreSQL documentation. This shows up in a newly-started session as the value of the 'TimeZone' session parameter when the PGTZ environment variable is not set.

  • It can be set using the PGTZ environment variable. This is used by libpq clients to send a set time zone command to the server upon connection. This will override the value from postgresql.conf so that this, too, then shows up in a newly-started session as the value of the 'TimeZone' session parameter.

  • It can be set in an ongoing session using the generic set timezone = <text literal> syntax that is used for every session environment parameter. (This generic syntax does not allow a text expression.) Or it can be set with the timezone-specific set time zone interval <arg> syntax. This spelling also allow set time zone interval <text literal> (but again, a text expression is illegal). Additionally, it uniquely allows the set time zone interval <interval literal> syntax; notably, only this syntax for the interval value is allowed. You cannot use an arbitrary expression whose data type is interval. (See the section Four ways to specify the UTC offset.)

Here are examples of the three syntax variants:

set timezone = 'America/New_York';

set time zone 'Asia/Tehran';

set time zone interval '1 hour';

Note: If the supplied text literal isn't found in pg_timezone_names (see Rules for resolving a string that's intended to identify a UTC offset, then an attempt is made to parse it as POSIX syntax (see Directly using POSIX syntax). The result might surprise you. Try this:

deallocate all;
prepare stmt as
select '2008-07-01 13:00 America/Los_Angeles'::timestamptz;

set timezone = 'America/Los_Angeles';
execute stmt;

set timezone = '-07:00';
execute stmt;

set timezone = 'Foo-7';
execute stmt;

These are the results:

 2008-07-01 13:00:00-07

 2008-07-02 03:00:00+07

 2008-07-02 03:00:00+07

This brings a potential risk: an unnoticed typo that introduces a digit into an otherwise legal timezone name will silently produce a dramatically different effect than was intended. Yugabyte recommends that you avoid this risk. Notice the contrast between this outcome and the one that uses "-07:00" as in the section Specify the UTC offset explicitly within the text of a timestamptz literal or for make_timestamptz()'s timezone parameter below. Here, both "-07:00'" and "Foo-7" are interpreted as POSIX syntax. But below, only "Foo-7" is interpreted as POSIX syntax while "-07:00'" is interpreted as ISO 8601 syntax. See Recommended practice for specifying the UTC offset.

Specify the UTC offset using the 'at time zone' operator

This syntax is used:

  • either to decorate a plain timestamp value in order to convert it to a timestamptz value;

  • or to decorate a timestamptz value in order to convert it to a plain timestamp value;

The operator's legal operands are either a text expression or an interval expression. The operator syntax has a semantically identical function syntax, timezone(), with overloads for these indirect and direct ways of specifying the offset.

Here are examples of the four syntax variants:

drop function if exists timezone_name() cascade;
create function timezone_name()
  returns text
  language plpgsql
as $body$
begin
  -- Some logic would go here. Just return a manifest constant for this demo.
  return 'Europe/Amsterdam';
end;
$body$;

select make_timestamp(2021, 6, 1, 12, 13, 19) at time zone timezone_name();

select timezone(timezone_name(), make_timestamp(2021, 6, 1, 12, 13, 19));

select make_timestamptz(2021, 6, 1, 12, 13, 19, 'Europe/Helsinki') at time zone make_interval(hours=>4, mins=>30);

select timezone(make_interval(hours=>4, mins=>30), make_timestamptz(2021, 6, 1, 12, 13, 19, 'Europe/Helsinki'));

Specify the UTC offset explicitly within the text of a timestamptz literal or for make_timestamptz()'s 'timezone' parameter

Of course, the timestamptz literal can be an expression. Here's an example:

select ('1984-04-01 13:00 '||timezone_name())::timestamptz;

There's a possibility here, too, of interpreting a text value with an embedded digit. Try this:

set timezone = 'America/Los_Angeles';
\x on
with c as (select '2008-07-01 13:00' as ts)
select
  (select ((select ts from c)||' America/Los_Angeles')::timestamptz) as "Spelling 'LA' timezone in text",
  (select ((select ts from c)||' -07:00'             )::timestamptz) as "Attempting to specify the PDT offset",
  (select ((select ts from c)||' Foo99'              )::timestamptz) as "Specifying 'Foo99'";
\x off

This is the result:

Spelling 'LA' timezone in text       | 2008-07-01 13:00:00-07
Attempting to specify the PDT offset | 2008-07-01 13:00:00-07
Specifying 'Foo99'                   | 2008-07-05 09:00:00-07

Notice the contrast between this outcome and the one that uses "-07:00" as in the section Specify the UTC offset using the session environment parameter TimeZone above. Here, only "Foo-7" is interpreted as POSIX syntax while "-07:00'" is interpreted as ISO 8601 syntax. But above, both "-07:00'" and "Foo-7" are interpreted as POSIX syntax. So there's all the more reason to embrace what the section Recommended practice for specifying the UTC offset describes.

The same rules apply if you use the make_timestamptz() built-in function.

set timezone = 'America/Los_Angeles';
\x on
select
  (select make_timestamptz(2008, 7, 1, 13, 0, 0, 'America/Los_Angeles')) as "Spelling 'LA' timezone in text",
  (select make_timestamptz(2008, 7, 1, 13, 0, 0, '-07:00'             )) as "Attempting to specify the PDT offset",
  (select make_timestamptz(2008, 7, 1, 13, 0, 0, 'Foo99'              )) as "Specifying 'Foo99'";
\x off

The result is identical to that of the query, immediately above, that used the timestamptz literal.