TIMESTAMP_TZ scalar function
The TIMESTAMP_TZ function returns a TIMESTAMP WITH TIME ZONE value from the input arguments.
The schema is SYSIBM.
- expression-1
- An expression that returns a value of one of the following built-in data types:
- a timestamp without time zone
- a timestamp with time zone
- a character string
- a graphic string
- It must not be a CLOB or DBCLOB
- Its value must be a valid string representation of a timestamp without a time zone or a timestamp with a time zone value
- It must have an actual length that is not greater than 255 bytes
If expression-2 is specified, expression-1 must be a timestamp without a time zone, or a string representation of a timestamp without a time zone.
- expression-2
- An expression that returns a character string or a graphic string.
If expression-2 is a character string or graphic string, it must not be a CLOB or DBCLOB, and its value must be a valid string representation of a time zone in the format of '±th:tm' with values ranging from -24:00 to +24:00, where th represents time zone hour and tm represents time zone minute.
The result of the function is equivalent to invoking the CAST specification, as indicated in the following table:
TIMESTAMP_TZ function syntax | Equivalent CAST specification syntax |
---|---|
TIMESTAMP_TZ(timestamp_wo_tz) |
CAST(timestamp_wo_tz AS TIMESTAMP WITH TIME ZONE) |
TIMESTAMP_TZ(timestamp_wo_tz, n) |
CAST(timestamp_wo_tz AS TIMESTAMP(n) WITH TIME ZONE) |
TIMESTAMP_TZ(timestamp_wo_tz, timezone) |
CAST(CONCAT(VARCHAR(timestamp_wo_tz, timezone) AS TIMESTAMP WITH TIME ZONE) |
TIMESTAMP_TZ(timestamp_wo_tz, timezone, n) |
CAST(CONCAT(VARCHAR(timestamp_wo_tz, timezone) AS TIMESTAMP(n) WITH TIME ZONE) |
TIMESTAMP_TZ(timestamp_w_tz) |
CAST(timestamp_w_tz AS TIMESTAMP WITH TIME ZONE) |
TIMESTAMP_TZ(timestamp_w_tz, n) |
CAST(timestamp_w_tz AS TIMESTAMP(n) WITH TIME ZONE) |
TIMESTAMP_TZ(timestamp_w_tz, timezone) |
N/A |
TIMESTAMP_TZ(timestamp_w_tz, timezone, n) |
N/A |
|
When a string representation of a timestamp is a single-byte character set (SBCS) with a CCSID that is not the same as the default CCSID for SBCS data, that value is converted to the default CCSID for SBCS data before it is interpreted and converted to a timestamp value.
Notes
- Syntax alternatives:
- If only one argument is specified, the CAST specification should be used to ensure maximal portability. For more information, see CAST specification
FROM_TZ can be specified as a synonym for TIMESTAMP_TZ when TIMESTAMP_TZ specifies both expression-1 and expression-2.
Examples
- Example 1:
- Assume that TIMES is a host variable with the value 2008-02-29-20.00.00.000000 and that TZ is a host variable with the value -3:00. Convert the value of TIMES and TZ to a timestamp with time zone.
The host variable TIMESZ is set with the value that represents the timestamp with time zone as 2008-02-29-20.00.00.000000 -03:00.SET :TIMESZ = TIMESTAMP_TZ(:TIMES, :TZ);