Four ways to specify the UTC offset

The UTC offset is, ultimately, expressed as an interval value. It can be specified in various different ways as this page explains.

Yugabyte recommends using only a timezone name or an explicit 'interval' value to specify the 'UTC offset'.

See the section Recommended practice for specifying the UTC offset. It presents a sufficient way to achieve all the functionality that you could need while protecting you from the many opportunities to go wrong brought by using the native functionality with no constraints.

Directly as an interval value

To specify an interval value for the session's 'TimeZone' setting, you must use the set time zone <arg> syntax and not the set timezone = <arg> syntax. For example:

set time zone interval '-7 hours';

Notice that, in this syntax context, the interval value can be specified only using the type name constructor. Each of these attempts:

set time zone '-7 hours'::interval;

and:

set time zone make_interval(hours => -7);

causes this error:

42601: syntax error

This reflects an insuperable parsing challenge brought by the very general nature of the set statement. (For example, it has variants for setting the transaction isolation level.)

In contrast, the at time zone operator allows any arbitrary interval expression. Try this:

select '2021-05-27 12:00:00'::timestamp at time zone (make_interval(hours=>5) + make_interval(mins=>45));

You can also specify an interval value within the text of a timestamptz literal. But here, you use just the text that would be used with the ::interval typecast. Try this:

select '2021-05-27 12:00:00 -03:15:00'::timestamptz;

The same rule applies if you use the make_timestamptz() built-in function. Its timezone formal parameter has data type text. There is no overload where this parameter has data type interval.

Directly using POSIX syntax

The syntax is described in the PostgreSQL documentation in the appendix B.5. POSIX Time Zone Specifications. This allows you to specify the two UTC offset values, one for Standard Time and one for Summer Time, along with the "spring forward" and "fall back" moments. It's exceedingly unlikely that you will need to use this because, these days, everywhere on the planet falls within a canonically-named timezone that keys to the currently-understood rules for Daylight Savings Time from the indefinite past through the indefinite future. The rules are accessible to the PostgreSQL and YugabyteDB servers. (The source is the so-called tz database.) If a committee decision changes any rules, then the tz database is updated and the new rules are thence adopted into the configuration data for PostgreSQL and YugabyteDB. Look at the tables on these two pages: Real timezones that observe Daylight Savings Time; and Real timezones that don't observe Daylight Savings Time.

Executing show timezone after it has been set to an explicit interval value reports back using POSIX syntax—albeit a simple form that doesn't specify Daylight Savings Time transitions. Try this:

set time zone interval '-07:30:00';
show timezone;

This is the result:

 <-07:30>+07:30

This is a legal argument for set timezone thus:

set timezone = '<-07:30>+07:30';
show timezone;

Of course, the result is exactly the same as what you set. It turns out that almost any string that contains one or more digits can be interpreted as POSIX syntax. Try this:

set timezone = 'FooBar5';
show timezone;

This is the result:

 TimeZone
----------
 FOOBAR5

You can easily confirm that FOOBAR5 is not found in any of the columns (pg_timezone_names.name, pg_timezone_names.abbrev, or pg_timezone_abbrevs.abbrev) where timezone names or abbreviations are found.

Now see what effect this has, like this:

\set bare_date_time '\'2021-04-15 12:00:00\''
select :bare_date_time::timestamptz;

This is the result:

 2021-04-15 12:00:00-05

POSIX takes positive numbers to mean west of the Greenwich Meridian. And yet the PostgreSQL convention for displaying such a UTC offset is to show it as a negative value. (See ISO 8601.) This seems to be the overwhelmingly more common convention. Internet searches seem always to show timezones for places west of Greenwich with negative UTC offset values. Try this:

set time zone interval '-5 hours';
select :bare_date_time::timestamptz;

Here, the PostgreSQL convention was used to specify the UTC offset value. Using the same :bare_date_time text literal, the result, 2021-04-15 12:00:00-05, is identical to what it was when the timezone was set to FooBar5.

Next, try this:

set timezone = 'Foo5Bar';
select :bare_date_time::timestamptz;

Now the result has changed:

2021-04-15 12:00:00-04

What? With the timezone set to FooBar5, the result of casting 2021-04-15 12:00:00 to timestamptz has a UTC offset value of negative five hours. But with the timezone set to Foo5Bar, the result of casting the same plain timestamp value to timestamptz has a UTC offset value of negative four hours. You can guess that this has something to do with the way POSIX encodes Daylight Savings Time rules—and with the defaults that are defined (in this case, Summer Time "springs forward" by one hour) when the tersest specification of Daylight Savings Time rules is given by using arbitrary text (in the example, at least) after the last digit in the POSIX text.

Indirectly using a timezone name

This is overwhelmingly the preferred approach because it's this, and only this, that brings you the beneficial automatic mapping to the UTC offset value that reigns at the moment of execution of a sensitive operation according to the rules for Daylight Savings Time that the name keys to in the internal representation of the tz database. (See the section Scenarios that are sensitive to the UTC offset or explicitly to the timezone.) The names are automatically looked up in the pg_timezone_names catalog view. See the section Rules for resolving a string that's intended to identify a UTC offset

Indirectly using a timezone abbreviation

Avoid using this approach.

Though this approach is legal, Yugabyte strongly recommends that you avoid using it.

The best that this approach can do for you is to bring you a fixed value for the UTC offset that's independent of the moment of lookup. But this is an exceedingly rare use case. If this is what you want, you can do it in a self-documenting way by specifying the UTC offset Directly as an interval value as was explained above. Moreover, there are other risks brought by the attempt to use a timezone abbreviation to specify a UTC offset. See the section Rules for resolving a string that's intended to identify a UTC offset.