8.9.10 TIMEZONE
Converts time stamp data with time zone to time stamp data without time zone based on the specified time zone.
- Organization of this subsection
(1) Specification format
scalar-function-TIMEZONE ::= TIMEZONE(time-zone-string,target-data)
time-zone-string ::= {literal | dynamic-parameter}
target-data ::= value-expression
(2) Explanation of specification format
- time-zone-string:
-
Specifies the time zone. The target data (time stamp data with time zone) is converted to time stamp data without time zone based on the time zone specified here.
Specify the time zone string in one of the following formats. Other formats are not supported.
-
±hh:mm format
Specify hours in hh and minutes in mm. For details about the ±hh:mm format, see the description "Time stamp literal (with time zone)" in 6.3.2 Description format of literals.
-
Time zones from the Time Zone Database managed by IANA (Internet Assigned Numbers Authority)
-
UTC (Coordinated Universal Time)
-
Time zone abbreviations
Examples of time zone string values are shown in the following table.
Table 8‒33: Examples of time zone string values Examples of time zone string values
Assumed time zone at conversion
±hh:mm format
'+09:00'
+09:00
'-03:00'
-03:00
Time zones from the Time Zone Database managed by IANA
'Asia/Tokyo'
+09:00
'Europe/Berlin'
+01:00 (standard time)
+02:00 (daylight saving time)
UTC
'UTC'
+00:00
Time zone abbreviations
'JST'
+09:00
'CST'
-05:00
'IST'
+05:30
The following additional rules apply:
-
Specify a literal or a dynamic parameter for the time zone string.
-
The data type of the time zone string must be either CHAR or VARCHAR.
-
The length of the time zone string must be up to 100 bytes.
-
If a dynamic parameter is specified alone for the time zone string, the assumed data type of the dynamic parameter is VARCHAR(100).
-
- target-data:
-
Specifies the data to be converted.
The following rules apply:
-
The data type of the target data must be either TIMESTAMP WITH TIME ZONE, CHAR, or VARCHAR. However, if CHAR or VARCHAR is used, only character string literals that follow the predefined input representation of the predefined character-string representation representing a time stamp (predefined input representation representing a time stamp with time zone) can be specified. For details about the predefined input representation, see (3) Predefined character-string representation of time stamps in 6.3.3 Predefined character-string representations.
-
A dynamic parameter cannot be specified alone for the target data.
-
(3) Rules
-
The execution result returns the target data (time stamp data with time zone) converted to time stamp data without time zone based on the time zone specified by the time zone string.
-
The data type of the execution result is TIMESTAMP WITHOUT TIMEZONE. The fractional seconds precision is the same as that of the target data.
-
If the value of the execution result exceeds the range of the TIMESTAMP type, the SQL statement results in an error.
-
The value of the execution result will not have a NOT NULL constraint (allows null values).
-
If the time zone string or the target data is null, the execution result is null.
-
The maximum number of scalar function TIMEZONE specifications in a single SQL statement is 1,000. If a viewed table is specified in the SQL statement, the number of TIMEZONE specifications in the CREATE VIEW statement is also added. The total must not exceed 1,000.
(4) Examples
- Examples
-
Column C1 of table T1 stores time stamp data with time zone (data of type TIMESTAMP WITH TIME ZONE). This data is converted to time stamp data without time zone (data of type TIMESTAMP WITHOUT TIME ZONE).
-
When converting to time stamp data without time zone in Japan Standard Time:
SELECT TIMEZONE ('+09:00',"C1") FROM "T1" SELECT TIMEZONE ('Asia/Tokyo',"C1") FROM "T1" SELECT TIMEZONE ('JST',"C1") FROM "T1"If the data in column C1 is 2026/01/01 12:00:00+06:00, the execution result will be 2026/01/01 15:00:00.
-
When converting to time stamp data without time zone in Coordinated Universal Time:
SELECT TIMEZONE ('UTC',"C1") FROM "T1"If the data in column C1 is 2026/01/01 12:00:00+06:00, the execution result will be 2026/01/01 06:00:00.
-