TIMEZONE scalar function

The TIMEZONE scalar function converts a date and time in one timezone into a timestamp in another timezone.

Read syntax diagramSkip visual syntax diagram TIMEZONE ( datetime-expression , from-timezone , to-timezone )

The schema is SYSIBM.

datetime-expression
An expression that returns a value of data type DATE, TIMESTAMP, CHAR, or VARCHAR. In a Unicode database, the expression can also be of data type GRAPHIC or VARGRAPHIC. A value of data type CHAR, VARCHAR, GRAPHIC, or VARGRAPHIC must be a valid string that is accepted by the TIMESTAMP scalar function scalar function. Such a string is implicitly cast to a timestamp before conversion.
from-timezone
An expression that specifies the time zone of the value returned by the input datetime expression.
to-timezone

An expression that specifies the time zone of the output timestamp.

The value returned by a to-timezone or from-timezone expression:
  • Must be a built-in character string data type (in a Unicode database, it can also be a graphic string data type) that contains a time zone name as specified in the Internet Assigned Numbers Authority (IANA) time zone database (SQLSTATE 22546).
  • Cannot be a FOR BIT DATA subtype (SQLSTATE 42815).
If the value is not of data type VARCHAR, it is cast to VARCHAR before the TIMEZONE function is evaluated.
The standard format for a time zone name in the IANA database is area/location, where:
area
The English name of a continent or ocean, or the special area Etc.
location
The English name of a location within the specified area. This is usually a city or a small island.
For example:

"America/Toronto"  [The North American city of Toronto]
"Asia/Sakhalin"    [The Asian island of Sakhalin]  
"Etc/UTC"          [Coordinated Universal Time]

For a complete list of valid time zone names and the rules associated with those time zones, refer to the IANA time zone database. The database server uses version 2010c of the IANA time zone database. If a newer version of the IANA time zone database is required, contact IBM support.

The precision of the output timestamp depends on the data type of the value returned by the input datetime expression:
  • If the input value is a timestamp, the output value is a timestamp with the same precision as the input value.
  • If the data type of the input value is DATE, the data type of the output value is TIMESTAMP(0).
  • Otherwise, the data type of the output value is TIMESTAMP(6).

If any argument of the function can be null, the result can be null. If any argument is null, the result is the null value.

Examples

  • The data type of column col1 of table T1 is TIMESTAMP(3), so the data type of the output of the following statement is also TIMESTAMP(3):
    select TIMEZONE(col1, 'America/New_York', 'America/Los_Angeles')from T1;
    This statement returns: 2016-12-19-14.00.00.123.
  • The data type of column col3 of table T5 is DATE, so the data type of the output of the following statement is TIMESTAMP(0):
    select TIMEZONE(col3, 'America/New_York', 'America/Los_Angeles')from T5;
    This statement returns: 2016-07-14-23.00.00.
  • The input of each of the following statements is a string literal, so the data type of their output is TIMESTAMP(6):
    values TIMEZONE('2016-09-24 17:00:00.12345678', 'America/New_York', 'America/Los_Angeles');
    This statement returns: 2016-09-24-17.00.00.123456.
    values TIMEZONE('2016-09-24 17:00:00.123', 'America/New_York', 'America/Los_Angeles');
    This statement returns: 2016-09-24-17.00.00.123000.