CONVERT_TIMEZONE
Description
CONVERT_TIMEZONE converts a given date from one specified timezone to another. The function takes into account the Daylight Saving Time.
Output column type: DATE
Syntax
CONVERT_TIMEZONE ( table.date_column [, from_timezone_id], to_timezone_id )
Operator accepts the following timezone formats:
"Area/Location", e.g. "America/New_York"
Time zone abbreviation, e.g. "CET"
Administrative time zones with offset, e.g. "Etc/GMT-3"
More details about time zone identifiers can be found here.
Daylight Saving Time
Changing clocks between the Daylight Saving Time to the Standard Time and vice versa produces the situations when local time doesn't have an exact and unique mapping in a given time zone. For example, changing the clock from the Standard Time to the Daylight Saving Time with the 1 hour saving window (for example in Germany), produces in this case a 1 hour period window that "doesn't exist".
Therefore, the timestamp specified within the described time window will be mapped to the time point that will happen again but already in the chronological order. Operator behavior: a "non existent" time point is considered as a timestamp with the time difference after change to the DST from the Standard Time for the conversion.
In the situation when the clock changes from the Daylight Saving Time back to the Standard Time, the time points that are in the time window that was "rolled back" after switch will happen two times - the first one in a chronological order before time change and the second one after the time change.
Therefore, the timestamp specified within the described time window will be mapped to the time point that already happened in the chronological order. Operator behavior: an "ambiguous" time point is considered as a timestamp with the time difference before change from the DST to the Standard Time for the conversion.
Please see the examples for the behavior described below.
NULL handling
If date column is NULL, the result will be NULL as well. If date column contains NULL values, the operator will return null as the result of conversion for these values.
Example
[1] Conversion of the input date column from the time zone 'America/New_York' to the time zone 'Europe/Berlin'. | ||||||||||||
| ||||||||||||
|
[2] The example of conversion from the time zone 'America/New_York' to the time zone 'UTC' after the USA switches from the Standard Time to the Daylight Saving Time on 2022-03-13 at 02:00. After the clock change, the time difference in New York becomes UTC-4h instead of UTC-5h. | ||||||||||||
| ||||||||||||
|
[3] Conversion of the input date column from the time zone 'America/New_York' to the time zone 'Europe/Berlin' after the USA switches to the Daylight Saving Time on March, 13th at 02:00. As Germany switches to the Daylight Saving Time on 2022-03-27, the time difference between Berlin and New York within the time period 2022-03-13 - 2022-03-27 is 5 hours instead of 6. This example shows the behavior for timestamps in the hour between 02:00-03:00 on 2022-03-13, because this hour is non-existing (i.e., it was "skipped") due to the switch to the Daylight Saving Time in the USA. In this case it is considered that after 2022-03-13 02:00:00.000 time becomes 1 hour more. | ||||||||||
| ||||||||||
|
[4] Conversion of the input date column from the time zone 'Europe/Berlin' to the time zone 'America/New_York' after Germany switches from the Daylight Saving Time to the Standard Time on 2022-10-30 at 03:00. As America switches from the Daylight Saving Time to the Standard Time on 2022-11-06, the time difference between Berlin and New York within the time period 2022-10-30 - 2022-11-06 becomes 5 hours instead of 6. This example shows the behavior for timestamps in the hour between 02:00-03:00 on 2022-10-30, because this hour is ambiguous (i.e., it "happened twice") due to the switch back to the Standard Time in Germany. In this case is considered that after 2022-10-30 03:00:00.000 time becomes 1 hour less, but before the time difference was still 6 hours. | ||||||||||
| ||||||||||
|
[5] Conversion of the input date column from the default time zone 'UTC' to the time zone 'Europe/Berlin'. | ||||||||||||
| ||||||||||||
|
[6] Conversion of the input date column from the time zone 'Europe/Berlin' to the administrative time zone 'GMT+3'. | ||||||||||||
| ||||||||||||
|
[7] Conversion of the input date constant from the time zone 'Europe/Berlin' to the time zone 'America/Argentina/La_Rioja'. | ||||||
| ||||||
|