TO_UTC_TIMESTAMP scalar function

The TO_UTC_TIMESTAMP scalar function returns a TIMESTAMP that is converted to Coordinated Universal Time from the timezone that is specified by the timezone string. TO_UTC_TIMESTAMP is a a statement deterministic function.

Read syntax diagramSkip visual syntax diagram TO_UTC_TIMESTAMP ( expression , timezone-expression )

The schema is SYSIBM.

expression
An expression that specifies the timestamp that is in the timezone-expression 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.
timezone-expression
An expression that specifies the time zone that the expression is to be adjusted from. 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 Area/Location, where:
  • Area is the English name of a continent, ocean, or the special area 'Etc'.
  • Location is the English name of a location within the area; usually a city, or small island.
Examples:
  • America/Toronto
  • Asia/Sakhalin
  • 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 to the Coordinated Universal Time time zone from 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.

Examples

  1. Convert the timestamp '1970-01-01 00:00:00' to the Coordinated Universal Time timezone from the 'America/Denver' timezone. The following returns a TIMESTAMP with the value '1970-01-01 07:00:00'.
       TO_UTC_TIMESTAMP(TIMESTAMP'1970-01-01 00:00:00', 'America/Denver')
  2. The database administrator created a read-only global variable, SERVER_TIMEZONE, which contains the server's timezone. In this example, the SERVER_TIMEZONE user-defined global variable is set to 'America/Denver'.

    Convert the timestamp '1970-01-01 00:00:00' to the Coordinated Universal Time timezone from the server's timezone. The following returns a TIMESTAMP with the value '1970-01-01 07:00:00'.

       TO_UTC_TIMESTAMP(TIMESTAMP'1970-01-01 00:00:00', SERVER_TIMEZONE)