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

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

Date Time

Time Series

Accepted answers

binu_acs

@Austin004lasrado One way of doing this

CST_1018.yxmd

DataNath

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.

Convert DateTime to Singapore time.yxmd

All comments

binu_acs

@Austin004lasrado One way of doing this

CST_1018.yxmd

DataNath

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

Convert DateTime to Singapore time.yxmd

Austin004lasrado

thank you so much @binu_acs 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.

binu_acs

@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 @binu_acs , 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.

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703