Convert UTC / GMC to local time of locations in your data

[WayneWooldridge]

Suppose you have a datetime stamp in a dataset for the timezone where you are. This dataset includes data for locations in timezones other than the one you're in and you want to convert your datetime stamp to reflect the local timezones of the locatons in your data.

In the attached example, we'll pretend you're in the Eastern Standard Timezone and your datetime stamps are EST. Your data includes the following locations:

Irvine, CA

San Mateo, CA

Broomfield,CO

Irving, TX

Naperville, IL

London, UK

Sydney, NSW, Australia

Copenhagen, Denmark

Munich, Germany

Toronto, Ontario, Canada

We'll convert your EST datetime stamp to the local timezones of these locations. This solution will use latitude / longitude coordinates to determine the local timezones. Your data will need have lat/long coordinates for each of your locations.

The solution will use a Google API to find the UTC time offsets. Depending on the date, this could include an adjustment for daylight savings as well. To read more about the API being used, visit https://developers.google.com/maps/documentation/timezone/intro#Introduction. This site will give you information about which parameters are used to make an API call, and what data is returned. This is informational since Alteryx handles the data call and data engineering.

The Google Maps Time Zone API application (and most API applications in general) require some form of authentication. This means you will need to get an API key. It’s free to get a key, but you are limited to the number of calls you can make per day (I believe it’s currently set at 5K. If you are going to have more than 5K lines of data, let me know; we may be able to come up with something else). Go to https://developers.google.com/maps/documentation/timezone/get-api-key#key and look for this:

Click on get a key. A new browser tab will open that looks like this:

Go ahead and click Continue. You’ll see a message in a black box that say ‘Creating a new project’. A new window looks like this:

You can give your key any name you want, but you will need to enter your IP address. To find your IP address, go to https://www.whatismyip.com/. Look for ‘Your IP Address Is:’ and copy/paste your IP. Click Create. It says it may take up to 5 minutes for settings to take effect.

Once you have your API key, copy and save it somewhere like NotePad. You’ll need to enter your API key in the macro configuration window. Once you enter your key and save the workflow, you shouldn’t have to reenter it again.

Your input data looks like this:

Where the DateTimeStamp is in EST. The goal is change EST to local of time of each location. Attach your input file to the 'UTC to Local Time Converter Example' macro.

Replace 'YOUR_API_KEY' with the API Key you acquired and pasted/saved in NotePad (from above):

The browser results show the results from the conversion:

NOTE: the attached example was developed on Alteryx Designer v10.1.7.1288

The UTC to Local Time Converter Example macro will make the appropriate daylight savings adjustment.

[keithhelfrich]

Hi Wayne,

I'd like to use this macro but way you've documented is confusing.  If my data has all timestamps in UTC and I want to convert those into PST, then which values do I need to send into the macro for Location, Lat and Lon?

Thanks!

Keith

[WayneWooldridge]

You'll need:

Location

Latitude

Longitude

DateTimeStamp

if you plan to use the macro 'as is'.  You can always change the template in the Macro Input tool to match your data as long those 4 fields are included in your dataset.  If you don't have Lat/Lon codes, you can use the Street Geocoder to get those. 

You'll need to update the Text Input file in the 'Eastern Time' box to reflect PST.  Using any PST location, replace what is in the ETLatitude and ETLongitude fields with your PST Lat/Lon.  Again, use the Street Geocoder Tool to get Lat/Lon coordinates if you don't have them already.