Scenarios that are sensitive to the timezone/UTC offset [YSQL]

Scenarios that are sensitive to the UTC offset or explicitly to the timezone

All possible operations are inevitably executed in the context of a specified UTC offset because the default scheme for the TimeZone session setting ensures that this is never a zero-length text value or null. (See the section Specify the UTC offset using the session environment parameter TimeZone.) The TimeZone setting might specify the UTC offset directly as an interval value or it might specify it indirectly by identifying the timezone.

However, only three operations are sensitive to the setting:

  • The conversion of a plain timestamp value to a timestamptz value.
  • The conversion of a timestamptz value to a plain timestamp value.
  • Adding or subtracting an interval value to/from a timestamptz value.

Converting between plain timestamp values and timestamptz values

The detail is explained in the section Sensitivity of converting between timestamptz and plain timestamp to the UTC offset. That section defines the semantics of the conversions.

  • Other conversions where the source or target data type is timestamptz exhibit sensitivity to the UTC offset; but this can always be understood as a transitive sensitivity to the fundamental timestamptz to/from plain timestamp conversions. The section Typecasting between values of different date-time datatypes calls out all of these cases. Here is an example:

    timestamptz_value::date = (timestamptz_value::timestamp)::date

  • You can convert between a timestamptz value and a plain timestamp value using either the ::timestamp typecast or the at time zone operator. The former approach is sensitive to the current TimeZone session setting. And the latter approach, because the UTC offset is specified explicitly (maybe directly as an interval value or indirectly via an identified timezone) is insensitive to the current TimeZone session setting.

  • The built-in function overloads timezone(timestamp, text) and timezone(interval, text) have identical semantics to the at time zone operator and it can be advantageous to prefer these. See the section Recommended practice for specifying the UTC offset.

  • You can create a timestamptz value explicitly using either the make_timestamptz() built-in or a text literal. In each case, you can identify the timezone, or supply an interval value, directly in the syntax; or you can elide this information and let it be taken from the current TimeZone session setting. The full explanations are given in the section Specify the UTC offset explicitly within the text of a timestamptz literal or for make_interval()'s 'timezone' parameter and in the text to timestamptz subsection on the Typecasting between values of different date-time datatypes page.

Adding or subtracting an interval value to/from a timestamptz value

The section The sensitivity of timestamptz-interval arithmetic to the current timezone defines the semantics. Briefly, the outcome of the operation is sensitive as follows:

  • The sensitivity is specific to adding or subtracting an interval value to or from exactly and only a timestamptz value.

  • The sensitivity is specific to only the dd value of the [mm, dd, ss] internal representation of an interval value.

  • Only the session's TimeZone setting matters. There is no explicit syntax, analogous to the at time zone operator or its equivalent timezone() function overloads to let you override the session's setting.

  • The potential sensitivity requires that the session's TimeZone setting identifies a timezone name, and not an explicit UTC offset.

  • The identified timezone must specify a Daylight Savings regime.

  • The range between the starting timestamptz value and the ending timestamptz value that the interval specifies must span either the "spring forward" moment or the "fall back" moment.