Advent of Code is back! Unwrap daily challenges to sharpen your Alteryx skills and earn badges along the way! Learn more now.

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

Time Stamp conversion from CST to Singapore - from Excel source file

Austin004lasrado
8 - Asteroid

hello All,

 

in excel we get the time in 13:00:00 format(24 hour format) which is in CST time zone, i am looking for this info.

1) how to to convert CST  to Singapore time zone?

2) can it handle dynamically considering daylight saving, if so how?

3) if not can we maintain a static file where we can easily update the time which we need to substract , if so how can we do that ?

 

Any help will be much appreciated

 

Regards,

Austin

9 REPLIES 9
binuacs
21 - Polaris

@Austin004lasrado One way of doing this

 

binuacs_0-1666080976212.png

 

DataNath
17 - Castor
17 - Castor

Hi @Austin004lasrado, have you tried something like this? I've just used DateTimeNow() rather than a field and so you'd need to replace that. The DateTimeToUTC() converts the field within to UTC at runtime https://help.alteryx.com/20221/designer/datetime-functions

 

From there, we can just use DateTimeAdd() to offset UTC by +8 hours, which returns SST.

 

DataNath_0-1666081035217.png

Austin004lasrado
8 - Asteroid

thank you so much @binuacs this is really helpfull,

 

Also, is there way we can handle this dynamically for cover Day light saving, like creating a static file where i mention how many hours to be added to the time provided in base file. Like in File A(base file) the time is 13:00:00 and in File b(Static file) based on daylight saving changes i update 13:00:00 or 12:00:00. etc. this will reduce the code change every time.

 

binuacs
21 - Polaris

@Austin004lasrado Sorry for the delay in response. This can be done as you suggested. I will create a workflow and send for your reference

Austin004lasrado
8 - Asteroid

Hello @binuacs , Sorry, I saw the message now, was there any luck on teh solution.. currently i used the suggestion suggested by @DataNath but it needs changes during daylight savings, which i am looking to avoid it

DataNath
17 - Castor
17 - Castor

Hey @Austin004lasrado, is your system set to the timezone you want? You could use the DateTimeToLocal() function if so:

 

DataNath_0-1683274673150.png

 

DateTimeToLocal(DateTimeToUTC(DateTimeNow()))

 

Again just replace DateTimeNow() with your Date field.

Austin004lasrado
8 - Asteroid

Hello @DataNath ,

i am sourcing the time from a file which is in Chicago time zone(CST) and i am looking to convert it in Singapore time zone, currently your First solution is working for me but i have to adjust 1 hour when daylight saving clock changes

DataNath
17 - Castor
17 - Castor

When does daylight savings start and end in your timezone? A quick google search suggests that Singapore doesn't observe daylight savings and UTC isn't impacted either. If you do need to adjust then you could just build some logic around the conversion where if the current date - DateTimeToday() - is within a certain date range then DateTimeAdd() X hours, else DateTimeAdd() Y hours.

Austin004lasrado
8 - Asteroid

So, My system time does not have any relevance here. It's the trading time done on exchange (Chicago hours) and Each trade line will have different time. since CST time has the day light saving and the straight conversion really does not work.

 

Labels
Top Solution Authors