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.

Inputs with time zone offsets are invalid.
Specified time zone must be a string literal of one of the supported time zone values.

Wrangle vs. SQL: This function is part of Wrangle, a proprietary data transformation language. Wrangle is not SQL. For more information, see Wrangle Language.

Basic Usage

Column reference values:

converttimezone(myTimestamp,'US/Mountain','US/Pacific')

Output: Returns the UTC values of the myTimestamp converted from US Mountain time zone to US Pacific time zone.

Syntax and Arguments

converttimezone(date, 'enum-timezone1','enum-timezone2')

Argument	Required?	Data Type	Description
date	Y	datetime	Name of Datetime column, Datetime literal, or function returning a Datetime value.
enum-timezone-string1, enum-timezone-string2	Y	string	Case-sensitive string literal value corresponding to the source or target time zone.

For more information on syntax standards, see Language Documentation Syntax Notes.

date

Name of a column containing Datetime values, a literal Datetime value, or a function returning Datetime values to convert.

Astuce

Use the DATEFORMAT function to wrap values into acceptable formats. See DATEFORMAT Function.

If an input value is invalid for Datetime data type, a null value is returned.

Column references with time zone offsets are invalid.
Missing values for this function in the source data result in missing values in the output.
Multiple columns and wildcards are not supported.

Usage Notes:

Required?	Data Type	Example Value
Yes	Datetime (column reference, function, or literal)	sourceTime

enum-timezone-string1, enum-timezone-string2

String literal value for the time zone 1) to convert from and 2) to convert to.

Note

These values are case-sensitive.

Example values:

'America/Puerto_Rico'
'US/Eastern'
'US/Central'
'US/Mountain'
'US/Pacific'
'US/Alaska'
'US/Hawaii'

Examples

Astuce

For additional examples, see Common Tasks.

Example - Time zone conversion

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, Designer Cloud 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	yyyymmdd*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','yyyymmdd*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

Dans cette section: