Skip to main content

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, Dataprep by Trifacta 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