topic Re: Covert different time zone to local time in Alteryx Designer Desktop Discussions

Covert different time zone to local time

Jennyman130 — Thu, 24 May 2018 05:08:23 GMT

Hi all,

I would like to change the date and time of various time zones in the data for performing the review easily, for example: 11/3/2017 4:15 PM AWST. The AWST here stands for Australian Western Standard Time, and I would like to covert it to local time - Hong Kong time. Is it possible please?

Many thanks in advance,

Jenny

Re: Covert different time zone to local time

StephenRuhl — Tue, 10 Apr 2018 12:48:46 GMT

Alteryx only has UTC to local datetime conversions built in, but you can use DateTimeAdd() to adjust for the difference in hours between known time zones. However, in your case Hong Kong is in the same time zone as AWST so this shouldn't be necessary.

Re: Covert different time zone to local time

Jennyman130 — Thu, 12 Apr 2018 11:49:46 GMT

Thanks Sruhl, good to know that there is way to get that work.

Is DateTimeAdd() a function in Formula or it's within Date/Time parse function? Would you mind to show me in work flow on how to use this function for my data format?

Many thanks,

Jenny

Re: Covert different time zone to local time

BenMoss — Thu, 12 Apr 2018 11:54:24 GMT

it's available within the formula tool.

Example attached.

Ben

Re: Covert different time zone to local time

StephenRuhl — Thu, 12 Apr 2018 11:56:38 GMT

Sure thing @Jennyman130. Please see the attached workflow.

Re: Covert different time zone to local time

Jennyman130 — Thu, 12 Apr 2018 12:00:05 GMT

Thanks so much all, but I got an error message saying the sample is a newer version of Alteryx, so I can't view it. :(

Is it possible to post as a screenshot please?

Many thanks,

Jenny

Re: Covert different time zone to local time

BenMoss — Thu, 12 Apr 2018 12:01:45 GMT

Handy trick.

You can downgrade a workflow by opening it up in a text editor and amending the version (outlined in line 2), to 11.5 for instance.

Ben

Re: Covert different time zone to local time

Jennyman130 — Thu, 12 Apr 2018 12:12:10 GMT

Sorry I tried to open it with a notepad, but the second line is like below. Doesn't seem quite like the format you mentioned.

Re: Covert different time zone to local time

andreea_aurica14 — Thu, 12 Apr 2018 12:18:08 GMT

Hello,

In formula tool you can use function DateTimeAdd([field],x,"Hours") where x is the time zone difference.

Re: Covert different time zone to local time

BenMoss — Thu, 12 Apr 2018 12:24:02 GMT

right yes, so you can simply overwrite 2018.1 to 11.5 or the correct version number and then save it.

Then you will be able to open the workflow.

ben

Re: Covert different time zone to local time

Jennyman130 — Thu, 12 Apr 2018 12:33:06 GMT

Many thanks! I can see it now.

However, my data includes the timezone SST/CET/GMT/EST which I have updated into the workflow below. And the local time depends on which time zone it is. In this case, do I need to split out the timezone into a different column first to get the formula work? And do I need to use If formula to make the formula work for each timezone?

Thanks,

jenny

Re: Covert different time zone to local time

Jennyman130 — Thu, 12 Apr 2018 17:05:00 GMT

Sorry if my explanation in the previous post was a bit confusing. But the final output I want is a separate column which shows the Hong Kong local time based on the data attached above.

I’m thinking to first use a regex parse function to split out the timezone, then use a if formula along with datetimeadd() function to get the local time. Does anyone know if this is the solution or there is an easier way to do it please?

Many thanks,
Jenny

Re: Covert different time zone to local time

StephenRuhl — Thu, 12 Apr 2018 18:06:07 GMT

It's probably simplest to break out the time zone into another field and use something like:

IF [Timezone] = "EST" THEN

DateTimeAdd([Datetime], 12, "hours"

ELSEIF [Timezone] = "other time" THEN

DateTimeAdd([Datetime], x, "hours"

...

ELSE

[DateTime]

ENDIF

Re: Covert different time zone to local time

BenMoss — Thu, 12 Apr 2018 18:33:31 GMT

I'd use a lookup table to join the offset data, this way it can be managed more effectively. Once you have the table, join on the common field (timezone) and then use the additional offset field in your datetimeadd() calculation.

Ben

Re: Covert different time zone to local time

Jennyman130 — Fri, 13 Apr 2018 01:45:58 GMT

Thanks for the great ideas!