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

                         (1)     
>>-+-function-invocation-----+---------------------------------->
   +-(expression)------------+   
   +-constant----------------+   
   +-column-name-------------+   
   +-variable----------------+   
   +-special-register--------+   
   +-scalar-fullselect-------+   
   +-case-expression---------+   
   '-cast-specification------'   

>--+-AT LOCAL----------------------------------+---------------><
   |                                     (1)   |   
   '-AT TIME ZONE--+-function-invocation-----+-'   
                   +-(expression)------------+     
                   +-constant----------------+     
                   +-column-name-------------+     
                   +-variable----------------+     
                   +-special-register--------+     
                   +-scalar-fullselect-------+     
                   +-case-expression---------+     
                   '-cast-specification------'

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 between 0 and 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.

Cast a timestamp for April 12, 2010 to the local time zone. Assume that the IMPLICIT TIME ZONE system parameter is set to '-8:00'.

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

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

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 SYSIBM.SYSDUMMY1;
```
Retrieve the value of the tstz column adjusted for the time zone +08:00:
```
SELECT tstz AT TIME ZONE '+08:00'
	FROM SYSIBM.SYSDUMMY1;
```

Retrieve the value of the tstz column adjusted for UTC:

SELECT tstz AT TIME ZONE '00:00'
	FROM SYSIBM.SYSDUMMY1;