Page tree

Versions Compared

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.
Comment: Published by Scroll Versions from space DEV and version r0810

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, 

D s product
 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:

Code Block
yyyy*mm*dd*HH:MM:SS

D trans
RawWrangletrue
p03Valueyyyy*mm*dd*HH:MM:SS
Typestep
WrangleTextsettype col: datetime datetimeType: 'Datetime','yy-mm-dd hh:mm:ss','yyyy*mm*dd*HH:MM:SS' type: Datetime
p01NameColumns
p01Valuedatetime
p02NameNew type
p02ValueDate/Time
p03NameDate/Time type
SearchTermChange column data type

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:

D trans
RawWrangletrue
p03ValueISMISMATCHED(datetime, ['Datetime','yy-mm-dd hh:mm:ss','yyyy*mm*dd*HH:MM:SS'])
Typestep
WrangleTextfilter type: custom rowType: single row: ISMISMATCHED(datetime, ['Datetime','yy-mm-dd hh:mm:ss','yyyy*mm*dd*HH:MM:SS']) action: Delete
p01NameCondition
p01ValueCustom formula
p02NameType of formula
p02ValueCustom single
p03NameCondition
p04ValueDelete matching rows
p04NameAction
SearchTermFilter 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:

D trans
RawWrangletrue
p03Value'datetimeUTC2Eastern'
Typestep
WrangleTextderive type: single value: CONVERTFROMUTC(datetime, 'US\/Eastern') as: 'datetimeUTC2Eastern'
p01NameFormula type
p01ValueSingle row formula
p02NameFormula
p02ValueCONVERTFROMUTC(datetime, 'US\/Eastern')
p03NameNew column name
SearchTermNew formula

This transformation now assumes that the date values are in US/Pacific time zone and converts them to UTC:

D trans
RawWrangletrue
p03Value'datetimePacific2UTC'
Typestep
WrangleTextderive type: single value: CONVERTTOUTC(datetime, 'US\/Pacific') as: 'datetimePacific2UTC'
p01NameFormula type
p01ValueSingle row formula
p02NameFormula
p02ValueCONVERTTOUTC(datetime, 'US\/Pacific')
p03NameNew column name
SearchTermNew formula

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:

D trans
RawWrangletrue
p03Value'datetimeAlaska2Hawaii'
Typestep
WrangleTextderive type: single value: CONVERTTIMEZONE(datetime, 'US\/Alaska', 'US\/Hawaii') as: 'datetimeAlaska2Hawaii'
p01NameFormula type
p01ValueSingle row formula
p02NameFormula
p02ValueCONVERTTIMEZONE(datetime, 'US\/Alaska', 'US\/Hawaii')
p03NameNew column name
SearchTermNew formula

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