Time zone specific expressions

Time zone specific expressions can be used to adjust timestamp values and character-string or graphic-string representations of timestamp values to specific time zones.

time-zone-specific-expressions

Notes:

¹ Must be a scalar function.

The first operand for time-zone-specific-expression must be an expression that returns the value of either a built-in timestamp or a built-in character or graphic string data type. If the first operand is a character string or graphic string, it must not be a CLOB or DBCLOB value and its value must be a valid character-string or graphic-string representation of a timestamp. For the valid formats of string representations of datetime values, see String representations of datetime values.

If the first operand of time-zone-specific-expression returns a TIMESTAMP WITHOUT TIME ZONE value, the expression is implicitly cast to TIMESTAMP WITH TIME ZONE before being adjusted to the indicated time zone.

AT LOCAL: Specifies that the timestamp value is to be adjusted for the local time zone using the SESSION TIME ZONE special register.
AT TIME ZONE: Specifies that the timestamp is to be adjusted for the time zone that is represented by the expression.
expression is a character or graphic string. It must not be a CLOB or DBCLOB value, and its value must be left justified and be of the form '±th:tm', where th represents the time zone hour between -12 and +14, and tm represents the time zone minutes in the range 0–59, with values ranging from -12:59 to +14:00. The value must not be the null value.

The expression returns a TIMESTAMP WITH TIME ZONE value in the indicated time zone.

Syntax alternatives: TIMEZONE can be specified as an alternative to TIME ZONE.

Example 1

CAST('2010-04-12-10:30:00.0 -5:00' AT LOCAL AS TIMESTAMP)

Returns: 2010-04-12-07:30:00.000000.

Example 2

Insert a timestamp value with a time zone into a table, tz, and retrieve it as a timestamp with the local time zone, with +08:00, and adjusted for UTC. Assume that table tz exists as follows:

CREATE TABLE tz(tstz TIMESTAMP WITH TIME ZONE);

INSERT INTO tz(tstz) VALUES(TIMESTAMP '2010-01-01-10.23.51-08:00');

Retrieve the value of the tstz column adjusted for the local time:
```
SELECT tstz AT LOCAL
    FROM tz;
```
Retrieve the value of the tstz column adjusted for the time zone +08:00:
```
SELECT tstz AT TIME ZONE '+08:00'
    FROM tz;
```
Retrieve the value of the tstz column adjusted for UTC:
```
SELECT tstz AT TIME ZONE '+0:00'
    FROM tz;
```