DATETIME Functions - Time Zone Conversion Between Local Machine and Worker

[elsastark]

Hello,

I am looking for a solution that can help with building out date logic that will work on both Designer and Gallery.

The issue that has come up is that our developers are spread out around the globe in different time zones and are using DATETIME logic to define end of business days and month end reporting. Their data is flowing through in their local time zones but when publishing to Gallery, our workers are set to UTC so their results are not accurate. 

Here is an example where we had an issue once the workflow was published to Gallery that is set to UTC time:

The input is a table with last business day dates where we are looking for the corresponding [Date] to the current month. 

This was ran at 8:15 CST on 11/30 and the server datetime function was trying to pull December.
 
 
DateTimeAdd(DateTimeFirstOfMonth(),0,"month")= DateTimeTrim([Date],"month")

8:15PM CST 11/30 is December for UTC. 
It would appear that its taking into account the server timezone. 
Is there a way to set a workflows or scripts timezone?

Basically, I am looking to solve two things:

• How to convert from UTC to a different time zone that observes daylight savings time in a way that doesn't hard code dates
• Have a workflow recognize what time zone it is in so that it will run successfully on both their local machines and on Gallery

If you have solutions for either one, it is greatly appreciated!

[clmc9601]

Hi @elsastark,

Have you considered storing all dates, even locally, as UTC? There are Alteryx functions to convert to and from UTC in the formula tool. 

DateTimeToLocal()

DateTimeToUTC() 

[elsastark]

Thanks @clmc9601 - I did look into that but I don't think it'll work as a lot of their logic depends on their data reflecting their specific time zone. If they adopt the standard of having all of the dates in UTC it will not work for their outputs as it will then need to still be converted back to their local time. Definitely, a good call out though where maybe part of the solution is to use the DateTimeToUTC() for when they are in Designer vs. running it on Gallery.

[clmc9601]

Hi @elsastark, ok, that makes sense. If it helps, you can also dynamically extract the timezone and calculate the difference from UTC from a local timezone. 

DateTimeFormat([Date], '%z') # this will return the timezone in standard format such as "America/Denver". 
#It works best when directly returning a timezone from the machine, such as:
DateTimeFormat(DateTimeNow(), '%z')

DateTimeDiff(DateTimeToUTC([Date]),[Date],'hours')

If any of this is helpful, please consider marking it as a solution so others may find it!

[elsastark]

Hi @clmc9601 - I'll test out if DateTimeFormat is helpful to identifying the timezone. I don't think this takes into account daylight savings time where it will change throughout the year how many hours you need to subtract form. 

[clmc9601 · Accepted solution]

Hi @elsastark,

I actually have implemented this DateTimeDiff() function in a way where I expect it to include Daylight savings. The computer will know at any point what the local time and UTC time are. Subtracting them will result in the number of hours between the timezones.

For example, I'm in mountain time. DateTimeDiff(DateTimeToUTC(DateTimeNow()),DateTimeNow(),'hours') will result in DateTimeDiff('2021-12-08 16-14:00','2021-12-08 09:14:00','hours'), which is a difference of 7. If daylight savings were currently the case, the function would result in DateTimeDiff('2021-12-08 16-14:00','2021-12-08 10:14:00','hours'),which is a difference of 6.

[elsastark · Accepted solution]

Thank you @clmc9601  - I think this solves how to convert to a time zone with daylight savings time without hard coding date. I am still struggling on how I can build a solution that would work on someone's designer (for example how yours is in mountain time) and then on our Server instance (UTC) time. I'm going to let the user know that they should just make sure everything should be converted to UTC time when using Gallery but I'll keep on the lookout to see if there is a better answer. 

[clmc9601]

@elsastark the functions DateTimeNow() and DateTimeStart() grab the system time of the user's computer/machine. If you mean having people/servers in different time zones using the same workflow to convert to UTC dynamically, I'd probably use a function to calculate the current difference from UTC and add that to any dates. This expression uses the function below wrapped in a DateTimeAdd().

DateTimeAdd([DateColumn],DateTimeDiff(DateTimeToUTC(DateTimeNow()),DateTimeNow(),'hours'),'hours')

This will calculate the difference from UTC (currently in mountain time it evaluates to 6 hours different) and then add it to whatever my date column would be. You can also add a - symbol in front of the second argument to subtract the number of hours, like if you need to go from UTC to local dynamically.

[TJM686]

I had to solve for a similar issue converting central time to UTC and found that doing it within the query on the input tool to be easiest. My inputs are from a Microsoft database and an Oracle database which had different syntax:

Microsoft:  substring (cast (YourFieldWithDate AT TIME ZONE 'Central Standard Time' AT TIME ZONE 'UTC' as nvarchar(23)),1,23) as Date

Oracle: from_tz (cast (YourFieldWithDate at time zone 'America/Chicago' as timestamp), 'UTC') as Date