Skip to main content

Celonis Product Documentation

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 )

  • table.date_column: date to convert, must be a column or a constant of type DATE

  • from_timezone_id: date timezone to convert from, must be a STRING constant. It is an optional parameter, default value is UTC if the value is omitted

  • to_timezone_id: date timezone to convert to, must be a STRING constant

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'.

Query

Column1 

         CONVERT_TIMEZONE ( "Table"."Timestamp" , 'America/New_York' , 'Europe/Berlin' )

Input

Output

Table

Timestamp : date

Fri Dec 31 1999 23:59:59.999

Sat Jan 01 2000 23:59:59.999

Tue Feb 01 2000 23:30:00.999

Result

Column1 : date

Sat Jan 01 2000 05:59:59.999

Sun Jan 02 2000 05:59:59.999

Wed Feb 02 2000 05:30:00.999

[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.

Query

Column1 

         CONVERT_TIMEZONE ( "Table"."Timestamp" , 'America/New_York' , 'UTC' )

Input

Output

Table

Timestamp : date

Sun Mar 20 2022 20:05:00.000

Sun Mar 13 2022 03:59:59.999

Fri Apr 01 2022 23:30:00.999

Result

Column1 : date

Mon Mar 21 2022 00:05:00.000

Sun Mar 13 2022 07:59:59.999

Sat Apr 02 2022 03:30:00.999

[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.

Query

Column1 

         CONVERT_TIMEZONE ( "Table"."Timestamp" , 'America/New_York' , 'Europe/Berlin' )

Input

Output

Table

Timestamp : date

Sun Mar 13 2022 02:15:00.000

Sun Mar 13 2022 03:50:59.999

Result

Column1 : date

Sun Mar 13 2022 07:15:00.000

Sun Mar 13 2022 08:50:59.999

[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.

Query

Column1 

         CONVERT_TIMEZONE ( "Table"."Timestamp" , 'Europe/Berlin' , 'America/New_York' )

Input

Output

Table

Timestamp : date

Sun Oct 30 2022 02:15:00.000

Sun Oct 30 2022 03:50:59.999

Result

Column1 : date

Sat Oct 29 2022 20:15:00.000

Sat Oct 29 2022 22:50:59.999

[5]

Conversion of the input date column from the default time zone 'UTC' to the time zone 'Europe/Berlin'.

Query

Column1 

         CONVERT_TIMEZONE ( "Table"."Timestamp" , 'Europe/Berlin' )

Input

Output

Table

Timestamp : date

Fri Dec 31 1999 23:59:59.999

Sat Jan 01 2000 23:59:59.999

Tue Feb 01 2000 23:30:00.999

Result

Column1 : date

Sat Jan 01 2000 00:59:59.999

Sun Jan 02 2000 00:59:59.999

Wed Feb 02 2000 00:30:00.999

[6]

Conversion of the input date column from the time zone 'Europe/Berlin' to the administrative time zone 'GMT+3'.

Query

Column1 

         CONVERT_TIMEZONE ( "Table"."Timestamp" , 'Europe/Berlin' , 'Etc/GMT-3' )

Input

Output

Table

Timestamp : date

Fri Dec 31 2021 23:59:59.999

Sat Jan 01 2022 23:59:59.999

Tue Feb 01 2022 23:30:00.999

Result

Column1 : date

Sat Jan 01 2022 01:59:59.999

Sun Jan 02 2022 01:59:59.999

Wed Feb 02 2022 01:30:00.999

[7]

Conversion of the input date constant from the time zone 'Europe/Berlin' to the time zone 'America/Argentina/La_Rioja'.

Query

Column1 

         CONVERT_TIMEZONE ( {d '2022-04-01 00:00:00' } , 'Europe/Berlin' , 'America/Argentina/La_Rioja' )

Input

Output

Result

Column1 : date

Thu Mar 31 2022 19:00:00.000