Converting time data between time zones
Use a Python expression to convert time data between time zones.
Introduction
Enter a Python expression in to the addfieldx or convertx transformation to convert time data from one time zone to another.
Use the addfieldx transformation to keep the data in the original time zone and add the converted times to a new column in the data extract.
Use the convertx transformation to overwrite the original data with the converted data.
The examples below demonstrate how the Python expressions can convert data from one time zone to another.
Prerequisites
Before you read this reference, perform all of the following actions:
-
Make sure your data extract contains time data.
-
Make sure you know the time zone in which the data is collected.
-
Find the name of the time zone to which you want to convert your data. For a full list of time zones, see this reference.
Configuring the Python expression
To convert time data from one time zone to another, enter the following Python expression in to the transformation:
__import__('pytz').timezone('current_timezone').localize(__import__('datetime').datetime.strptime({time_column},'current_time_format')).astimezone(__import__('pytz').timezone('new_timezone')).strftime('new_time_format')
Change the following parameters to configure the Python expression:
-
current_timezone- This is the current time zone of your time data. For a full list of time zones, see this reference. If your time data is in multiple time zones, consider creating a column that contains the relevant time zone to use in the Python expression (see Example 3 for a worked example). -
new_timezone- This is the new time zone to which you want to convert your time data. For a full list of time zones, see this reference. -
time_column- This is the name of the column that contains the time data to convert. -
current_time_format- This is the current format of the times to convert (time data often includes a date). Enter the time and date format to convert using time and date placeholders. For example, the time and date 01-03-2022 - 22:00 (1 March 2022, 10 pm) is in the time and date format%d-%m-%Y - %H:%M. -
new_time_format- This is the format for the converted time data. Enter the new date format using date placeholders.
See the examples section below for a worked example.
Rules for the Python expression
When configuring the Python expression for this use case, it is important to remember the following rules:
-
The column name parameter is case sensitive. Enter the name of the column exactly as it appears in the data extract.
-
Enter the name of the column between two braces (also known as curly brackets). For example, if the name of the column is Dates, enter
{Dates}. -
Use the Python time and date placeholders when entering a time and date format. For a full list of available placeholders, see the Python documentation. Some of the more common time placeholders are as follows:
-
%H- An hour as a zero-padded decimal number on a 24 hour clock. For example, 8am is08and 8pm is20. -
%M- A minute as a zero-padded decimal number. -
%S- A second as a zero-padded decimal number. -
%Z- The name of the time zone for the time data.
-
-
Enter times between two inverted commas. For example, enter
'.%H:%M:%S' -
The format of the time to convert must exactly match the format in the column containing the times. This includes the characters between the time placeholders. For example, if the times to convert are in the format HH:MM:SS, then the time format to enter into Python expression is
'. The time formats of%H:%M:%S''or%H.%M.%S'would fail because the characters between the time placeholders are wrong.%H-%M-%S
Example 1 - addfieldx
This example uses the addfieldx transformation to keep the original times and add the converted times to a new column in the data extract. The entered Python expression looks at a column called Times, converts the times from the MST time zone to CET and adds the converted times to a new column called EU Times in the data extract.
The date in the new time and date format lets you see when a converted time crosses over into the next day.
For more information on the addfieldx transformation configuration, see the addfieldx transformation reference.
Transformation configuration
-
Field Name
-
EU Times -
Python Expression
-
__import__('pytz').timezone('MST').localize(__import__('datetime').datetime.strptime({Times},'%d-%m-%Y %H:%M:%S')).astimezone(__import__('pytz').timezone('CET')).strftime('%d-%m-%Y %H:%M:%S') -
Field Index
-
-1
Data table before transformation
|
Conversion ID |
Times |
|---|---|
|
4310177778 |
21-01-2022 18:33:59 |
|
4328218379 |
21-01-2022 18:17:36 |
|
5260818288 |
21-01-2022 09:43:40 |
|
2028958545 |
21-01-2022 22:40:29 |
|
4393034066 |
21-01-2022 04:05:41 |
|
4989141466 |
21-01-2022 19:45:00 |
|
4911856467 |
21-01-2022 22:49:40 |
Data table after transformation
|
Conversion ID |
Times |
EU Times |
|---|---|---|
|
4310177778 |
21-01-2022 18:33:59 |
22-01-2022 02:33:59 |
|
4328218379 |
21-01-2022 18:17:36 |
22-01-2022 02:17:36 |
|
5260818288 |
21-01-2022 09:43:40 |
21-01-2022 17:43:40 |
|
2028958545 |
21-01-2022 22:40:29 |
22-01-2022 06:40:29 |
|
4393034066 |
21-01-2022 04:05:41 |
21-01-2022 12:05:41 |
|
4989141466 |
21-01-2022 19:45:00 |
22-01-2022 03:45:00 |
|
4911856467 |
21-01-2022 22:49:40 |
22-01-2022 06:49:40 |
Example 2 - convertx
This example uses the convertx transformation to overwrite the original times and dates with the converted times and dates. The entered Python expression looks at a column called Times and converts the dates from the MST time zone to CET time zone.
The date in the new time and date format lets you see when a converted time crosses over into the next day.
For more information on the convertx transformation configuration, see the convertx transformation reference.
Transformation configuration
-
Field
-
Times -
Python Expression
-
__import__('pytz').timezone('MST').localize(__import__('datetime').datetime.strptime({Times},'%d-%m-%Y %H:%M:%S')).astimezone(__import__('pytz').timezone('CET')).strftime('%d-%m-%Y %H:%M:%S') -
Fail on Error
-
Select this field.
Data table before transformation
|
Conversion ID |
Times |
|---|---|
|
4310177778 |
21-01-2022 18:33:59 |
|
4328218379 |
21-01-2022 18:17:36 |
|
5260818288 |
21-01-2022 09:43:40 |
|
2028958545 |
21-01-2022 22:40:29 |
|
4393034066 |
21-01-2022 04:05:41 |
|
4989141466 |
21-01-2022 19:45:00 |
|
4911856467 |
21-01-2022 22:49:40 |
Data table after transformation
|
Conversion ID |
Times |
|---|---|
|
4310177778 |
22-01-2022 02:33:59 |
|
4328218379 |
22-01-2022 02:17:36 |
|
5260818288 |
21-01-2022 17:43:40 |
|
2028958545 |
22-01-2022 06:40:29 |
|
4393034066 |
21-01-2022 12:05:41 |
|
4989141466 |
22-01-2022 03:45:00 |
|
4911856467 |
22-01-2022 06:49:40 |
Example 3 - addfieldx
This example uses the addfieldx transformation to keep the original times and add the converted times to a new column in the data extract. The data extract contains data across multiple time zones. This means the entered Python expression should look at the column containing the time zone data for the corresponding times. If a data extract does not contain this time zone data, add a column containing the time zone data using the addfieldx transformation.
The entered Python expression does the following:
-
Looks at a column called Times.
-
Converts the times from the time zones in the Time zone column to
CET. -
Adds the converted times to a new column called EU Times in the data extract.
The date in the new time and date format lets you see when a converted time crosses over into the next day.
For more information on the addfieldx transformation configuration, see the addfieldxtransformation reference.
Transformation configuration
-
Field Name
-
EU Times -
Python Expression
-
__import__('pytz').timezone({Timezone}).localize(__import__('datetime').datetime.strptime({Times},'%d-%m-%Y %H:%M:%S')).astimezone(__import__('pytz').timezone('CET')).strftime('%d-%m-%Y %H:%M:%S') -
Field Index
-
-1
Data table before transformation
|
Conversion ID |
Time zone |
Times |
|---|---|---|
|
4310177778 |
HST |
21-01-2022 18:33:59 |
|
4328218379 |
MST |
21-01-2022 18:17:36 |
|
5260818288 |
MST |
21-01-2022 09:43:40 |
|
2028958545 |
US/Pacific |
21-01-2022 22:40:29 |
|
4393034066 |
US/Pacific |
21-01-2022 04:05:41 |
|
4989141466 |
US/Eastern |
21-01-2022 19:45:00 |
|
4911856467 |
US/Eastern |
21-01-2022 22:49:40 |
Data table after transformation
|
Conversion ID |
Time zone |
Times |
EU Times |
|---|---|---|---|
|
4310177778 |
HST |
21-01-2022 18:33:59 |
22-01-2022 05:33:59 |
|
4328218379 |
MST |
21-01-2022 18:17:36 |
22-01-2022 02:17:36 |
|
5260818288 |
MST |
21-01-2022 09:43:40 |
21-01-2022 17:43:40 |
|
2028958545 |
US/Pacific |
21-01-2022 22:40:29 |
22-01-2022 07:40:29 |
|
4393034066 |
US/Pacific |
21-01-2022 04:05:41 |
21-01-2022 13:05:41 |
|
4989141466 |
US/Eastern |
21-01-2022 19:45:00 |
22-01-2022 01:45:00 |
|
4911856467 |
US/Eastern |
21-01-2022 22:49:40 |
22-01-2022 04:49:40 |