EXAMPLE - Time Zone Conversion Functions

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