EXAMPLE - Time Zone Conversion Functions
This example shows how you can use functions to convert Datetime values to different time zones.
Functions:
Item | Description |
|---|---|
CONVERTFROMUTC Function | Converts Datetime value to corresponding value of the specified time zone. Input can be a column of Datetime values, a literal Datetime value, or a function returning Datetime values. |
CONVERTTOUTC Function | Converts Datetime value in specified time zone to corresponding value in UTC time zone. Input can be a column of Datetime values, a literal Datetime value, or a function returning Datetime values. |
CONVERTTIMEZONE Function | Converts Datetime value in specified time zone to corresponding value second specified time zone. Input can be a column of Datetime values, a literal Datetime value, or a function returning Datetime values. |
ISMISMATCHED Function | Tests whether a set of values is not valid for a specified data type. |
Source:
row | datetime |
|---|---|
1 | 2020-03-15 |
2 | 2020-03-15 0:00:00 |
3 | 2020-03-15 +08:00 |
4 | 2020-03-15 1:02:03 |
5 | 2020-03-15 4:02:03 |
6 | 2020-03-15 8:02:03 |
7 | 2020-03-15 12:02:03 |
8 | 2020-03-15 16:02:03 |
9 | 2020-03-15 20:02:03 |
10 | 2020-03-15 23:02:03 |
Transformation:
When you import the above dates, Designer Cloud may not recognize the column as a set of dates. You can use the column menus to format the date values to the following standardized format:
yyyy*mm*dd*HH:MM:SS
Transformation Name | |
|---|---|
Parameter: Columns | datetime |
Parameter: New type | Date/Time |
Parameter: Date/Time type | yyyy*mm*dd*HH:MM:SS |
When the type has been changed, row 1 and row 3 have been identified as invalid. You can use the following transformation to remove these rows:
Transformation Name | |
|---|---|
Parameter: Condition | Custom formula |
Parameter: Type of formula | Custom single |
Parameter: Condition | ISMISMATCHED(datetime, ['Datetime','yy-mm-dd hh:mm:ss','yyyy*mm*dd*HH:MM:SS']) |
Parameter: Action | Delete matching rows |
When the Datetime values are consistently formatted, you can use the following transformations to perform conversions. The following tranformation converts the values from UTC to US/Eastern time zone:
Transformation Name | |
|---|---|
Parameter: Formula type | Single row formula |
Parameter: Formula | CONVERTFROMUTC(datetime, 'US\/Eastern') |
Parameter: New column name | 'datetimeUTC2Eastern' |
This transformation now assumes that the date values are in US/Pacific time zone and converts them to UTC:
Transformation Name | |
|---|---|
Parameter: Formula type | Single row formula |
Parameter: Formula | CONVERTTOUTC(datetime, 'US\/Pacific') |
Parameter: New column name | 'datetimePacific2UTC' |
The final transformation converts the date time values between arbitrary time zones. In this case, the values are assumed to be in US/Alaska time zone and are converted to US/Hawaii time zone:
Transformation Name | |
|---|---|
Parameter: Formula type | Single row formula |
Parameter: Formula | CONVERTTIMEZONE(datetime, 'US\/Alaska', 'US\/Hawaii') |
Parameter: New column name | 'datetimeAlaska2Hawaii' |
Results:
row | datetime | datetimeAlaska2Hawaii | datetimePacific2UTC | datetimeUTC2Eastern |
|---|---|---|---|---|
2 | 2020-03-15 00:00:00 | 2020-03-14 22:00:00 | 2020-03-15 07:00:00 | 2020-03-14 20:00:00 |
4 | 2020-03-15 01:02:03 | 2020-03-14 23:02:03 | 2020-03-15 08:02:03 | 2020-03-14 21:02:03 |
5 | 2020-03-15 04:02:03 | 2020-03-15 02:02:03 | 2020-03-15 11:02:03 | 2020-03-15 00:02:03 |
6 | 2020-03-15 08:02:03 | 2020-03-15 06:02:03 | 2020-03-15 15:02:03 | 2020-03-15 04:02:03 |
7 | 2020-03-15 12:02:03 | 2020-03-15 10:02:03 | 2020-03-15 19:02:03 | 2020-03-15 08:02:03 |
8 | 2020-03-15 16:02:03 | 2020-03-15 14:02:03 | 2020-03-15 23:02:03 | 2020-03-15 12:02:03 |
9 | 2020-03-15 20:02:03 | 2020-03-15 18:02:03 | 2020-03-16 03:02:03 | 2020-03-15 16:02:03 |
10 | 2020-03-15 23:02:03 | 2020-03-15 21:02:03 | 2020-03-16 06:02:03 | 2020-03-15 19:02:03 |