FROM_UTC_TIMESTAMP scalar function
The FROM_UTC_TIMESTAMP scalar function returns a TIMESTAMP that is converted from Coordinated Universal Time to the time zone specified by the time zone string. FROM_UTC_TIMESTAMP is a statement deterministic function.
The schema is SYSIBM.
- An expression that specifies the timestamp that is in the Coordinated Universal Time time zone. The expression must return a value that is a DATE, TIMESTAMP, CHAR, or VARCHAR data type. If expression does not contain time information, a time of midnight (00.00.00) is used for the argument. In a Unicode database, the expression can also be a GRAPHIC or VARGRAPHIC data type. CHAR, VARCHAR, GRAPHIC, and VARGRAPHIC are supported through implicit casting. If expression is a CHAR, VARCHAR, GRAPHIC, or VARGRAPHIC data type, it must be a valid string that is accepted by the TIMESTAMP scalar function.
- An expression that specifies the time zone that the expression is to be adjusted to. The
expression must return a value that is a built-in character string, numeric, or datetime data type.
In a Unicode database, the expression can also be a graphic string data type. Numeric and datetime
data types are supported through implicit casting. If the expression is not a VARCHAR, it is cast to
VARCHAR before the function is evaluated. The expression must not be a FOR BIT DATA subtype
(SQLSTATE 42815). timezone-expression must not be null if
expression is not null (SQLSTATE 42815).The value of the timezone-expression must be a time zone name from the Internet Assigned Numbers Authority (IANA) time zone database. The standard format for a time zone name in the IANA database is
Areais the English name of a continent, ocean, or the special area 'Etc'.
Locationis the English name of a location within the area; usually a city, or small island.
Etc/UTC(which represents Coordinated Universal Time)
For complete details on the valid set of time zone names and the rules that are associated with those time zones, refer to the IANA time zone database. The database server uses version 2010c of the IANA time zone database. Contact IBM® support if a newer version of the IANA time zone database is required.
The result of the function is a timestamp with the same precision as expression, if expression is a timestamp. If expression is a DATE, the result of the function is a TIMESTAMP(0). Otherwise, the result of the function is a TIMESTAMP(6).
The result can be null; if the expression is null, the result is the null value. The timezone-expression cannot be null if a not-null value was supplied for the expression (SQLSTATE 42815).
The result is the expression, adjusted from the Coordinated Universal Time time zone to the time zone specified by the timezone-expression. If the timezone-expression returns a value that is not a time zone in the IANA time zone database, then the value of expression is returned without being adjusted.
The timestamp adjustment is done by first applying the raw offset from Coordinated Universal Time of the timezone-expression. If Daylight Saving Time is in effect at the adjusted timestamp for the time zone that is specified by the timezone-expression, then the Daylight Saving Time offset is also applied to the timestamp.
Time zones that use Daylight Saving Time have ambiguities at the transition dates. When a time zone changes from standard time to Daylight Saving Time, a range of time does not occur as it is skipped during the transition. When a time zone changes from Daylight Saving Time to standard time, a range of time occurs twice. Ambiguous timestamps are treated as if they occurred when standard time was in effect for the time zone.
- Convert the Coordinated Universal Time timestamp '2011-12-25 09:00:00.123456' to the
'Asia/Tokyo' time zone. The following returns a TIMESTAMP with the value '2011-12-25
FROM_UTC_TIMESTAMP(TIMESTAMP '2011-12-25 09:00:00.123456', 'Asia/Tokyo')
- Convert the Coordinated Universal Time timestamp '2014-11-02 06:55:00' to the 'America/Toronto'
time zone. The following returns a TIMESTAMP with the value '2014-11-02
FROM_UTC_TIMESTAMP(TIMESTAMP'2014-11-02 06:55:00', 'America/Toronto')
- Convert the Coordinated Universal Time timestamp '2015-03-02 06:05:00' to the 'America/Toronto'
time zone. The following returns a TIMESTAMP with the value '2015-03-02
FROM_UTC_TIMESTAMP(TIMESTAMP'2015-03-02 06:05:00', 'America/Toronto')