Time Stamp conversion from CST to Singapore - from Excel source file
- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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
Solved! Go to Solution.
- Labels:
- Date Time
- Time Series
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
@Austin004lasrado Sorry for the delay in response. This can be done as you suggested. I will create a workflow and send for your reference
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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.