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 Enterprise Edition 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 | Change column data type |
|---|---|
| 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 | Filter rows |
|---|---|
| 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 | New formula |
|---|---|
| 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 | New formula |
|---|---|
| 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 | New formula |
|---|---|
| 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 |
This page has no comments.