Page tree

Trifacta Dataprep


Contents:

On April 28, 2021, Google is changing the required permissions for attaching IAM roles to service accounts. If you are using IAM roles for your Google service accounts, please see Changes to User Management.

   

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

  • CONVERTFROMUTC - Converts valid Datetime values from UTC time zone to a specified time zone. See CONVERTFROMUTC Function.
  • CONVERTTOUTC - Converts valid Datetime values from a specified time zone to UTC time zone. See CONVERTTOUTC Function.
  • CONVERTTIMEZONE - Converts valid Datetime values from one time zone to another. See CONVERTTIMEZONE Function.

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,  Cloud Dataprep by TRIFACTA INC. 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

This page has no comments.