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

[Austin004lasrado]

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

[binuacs · Accepted solution]

@Austin004lasrado One way of doing this

[DataNath · Accepted solution]

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.

[Austin004lasrado]

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]

@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]

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]

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

DateTimeToLocal(DateTimeToUTC(DateTimeNow()))

Again just replace DateTimeNow() with your Date field.

[Austin004lasrado]

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]

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]

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.