Page tree

Release 9.2


Contents:

   

This example shows how you can use functions to convert Datetime values to different time zones.

Functions:

ItemDescription
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:

rowdatetime
12020-03-15
22020-03-15 0:00:00
32020-03-15 +08:00
42020-03-15 1:02:03
52020-03-15 4:02:03
62020-03-15 8:02:03
72020-03-15 12:02:03
82020-03-15 16:02:03
92020-03-15 20:02:03
102020-03-15 23:02:03

Transformation: 


When you import the above dates,  Designer Cloud Powered by Trifacta 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:

rowdatetimedatetimeAlaska2HawaiidatetimePacific2UTCdatetimeUTC2Eastern
22020-03-15 00:00:002020-03-14 22:00:002020-03-15 07:00:002020-03-14 20:00:00
42020-03-15 01:02:032020-03-14 23:02:032020-03-15 08:02:032020-03-14 21:02:03
52020-03-15 04:02:032020-03-15 02:02:032020-03-15 11:02:032020-03-15 00:02:03
62020-03-15 08:02:032020-03-15 06:02:032020-03-15 15:02:032020-03-15 04:02:03
72020-03-15 12:02:032020-03-15 10:02:032020-03-15 19:02:032020-03-15 08:02:03
82020-03-15 16:02:032020-03-15 14:02:032020-03-15 23:02:032020-03-15 12:02:03
92020-03-15 20:02:032020-03-15 18:02:032020-03-16 03:02:032020-03-15 16:02:03
102020-03-15 23:02:032020-03-15 21:02:032020-03-16 06:02:032020-03-15 19:02:03

See Also for EXAMPLE - Time Zone Conversion Functions:

This page has no comments.