---
title: "Three syntax contexts that use the specification of a UTC offset"
url: "https://docs.yugabyte.com/stable/api/ysql/datatypes/type_datetime/timezones/syntax-contexts-to-spec-offset/"
---

# Three syntax contexts that use the specification of a UTC offset

Explains the three syntax contexts that use the specification of a UTC offset. [YSQL]

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

- [Using the session environment parameter *TimeZone*](#specify-the-utc-offset-using-the-session-environment-parameter-timezone "Using the session environment parameter TimeZone")
- [Using the *at time zone* operator](#specify-the-utc-offset-using-the-at-time-zone-operator "Using the at time zone operator")
- [Within the text of a *timestamptz* literal or for *make\_timestamptz()*'s *timezone* parameter](#specify-the-utc-offset-explicitly-within-the-text-of-a-timestamptz-literal-or-for-make-timestamptz-s-timezone-parameter "Within the text of a timestamptz literal or for make_timestamptz()'s timezone parameter")

## 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](https://www.postgresql.org/docs/15/runtime-config.html "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*](/stable/api/ysql/datatypes/type_datetime/timezones/ways-to-spec-offset/ "Four ways to specify the UTC offset").)

Here are examples of the three syntax variants:

```plpgsql
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*](/stable/api/ysql/datatypes/type_datetime/timezones/ways-to-spec-offset/name-res-rules/ "Rules for resolving a string that's intended to identify a UTC offset"), then an attempt is made to parse it as [POSIX syntax](https://www.postgresql.org/docs/15/datetime-posix-timezone-specs.html "POSIX syntax") (see [Directly using POSIX syntax](/stable/api/ysql/datatypes/type_datetime/timezones/ways-to-spec-offset/#directly-using-posix-syntax "Directly using POSIX syntax")). The result might surprise you. Try this:

```plpgsql
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:

```output
 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](#specify-the-utc-offset-explicitly-within-the-text-of-a-timestamptz-literal-or-for-make-interval-s-timezone-parameter "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](https://www.postgresql.org/docs/15/datetime-posix-timezone-specs.html "POSIX syntax"). But below, only *"Foo-7"* is interpreted as POSIX syntax while *"-07:00'"* is interpreted as [ISO 8601](https://en.wikipedia.org/wiki/ISO_8601 "ISO 8601") syntax. See [Recommended practice for specifying the *UTC offset*](/stable/api/ysql/datatypes/type_datetime/timezones/recommendation/ "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:

```plpgsql
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:

```plpgsql
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:

```plpgsql
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:

```output
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*](#specify-the-utc-offset-using-the-session-environment-parameter-timezone "Specify the UTC offset using the session environment parameter TimeZone") above. Here, only "Foo-7" is interpreted as [POSIX syntax](https://www.postgresql.org/docs/15/datetime-posix-timezone-specs.html "POSIX syntax") while "-07:00'" is interpreted as [ISO 8601](https://en.wikipedia.org/wiki/ISO_8601 "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*](/stable/api/ysql/datatypes/type_datetime/timezones/recommendation/ "Recommended practice for specifying the UTC offset") describes.

The same rules apply if you use the *make\_timestamptz()* built-in function.

```plpgsql
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.