TIMEZONE scalar function
The TIMEZONE scalar function converts a date and time in one timezone into a timestamp in another 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).
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.
"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):
This statement returns:select TIMEZONE(col1, 'America/New_York', 'America/Los_Angeles')from T1;
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):
This statement returns:select TIMEZONE(col3, 'America/New_York', 'America/Los_Angeles')from T5;
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):
This statement returns:values TIMEZONE('2016-09-24 17:00:00.12345678', 'America/New_York', 'America/Los_Angeles');
2016-09-24-17.00.00.123456
.
This statement returns:values TIMEZONE('2016-09-24 17:00:00.123', 'America/New_York', 'America/Los_Angeles');
2016-09-24-17.00.00.123000
.