Error on Gallery/Server DateTimeTrim

Question

I am running a formula in a workflow that I expect to trim to the first of the month.

DateTimeTrim([Metric Date],'month')

In a specific example where the metric date is "2022-08-31" I am getting different answers between server and desktop. Desktop is returning 2022-08-01 and server is returning 2022-09-01.

I have run multiple different iterations of this calculation

DateTimeTrim([Metric Date],'firstofmonth')

DateTimeAdd([Metric Date],-(DateTimeDay([Metric Date])-1),"days")

I have also tried saving new versions to server, ensuring the published version is the new version, running it, getting the wrong answer, downloading the same latest version out of gallery running it on desktop and then getting the right answer.

I am at a full conclusion that gallery is incorrect, in how it interprets this calculation. What is the fix? I need this workflow to run on a schedule, and be correct.

s_graham · Answer

did another fully independent test. Here are the steps I took

1. Created in designer a listing of DateTime every minute between "2020-01-01 00:00:00" and "2020-12-31 23:59:00" using Generate Rows function, I then directly uploaded this listing to Snowflake. In Designer all the dates are correct on this workflow. See attached Step 1 Results exported from a browse in Designer.

2. Pulled the table from snowflake using the SQL worksheet in snowflake, exported to CSV, and my times are already off by two hours. The table now reads from "2019-12-31 22:00:00" to "2020-12-31 21:59:00". See Attached Step 2 Results.

3. Ran a workflow in Alteryx Gallery that takes the Snowflake table and adds a column > DateTimeTrim([DateTime],"day") <, I then reloaded this to Snowflake under a new table name, and exported this table in the SQL Worksheet in snowflake. See attached Step 3 Results. My times are now off by 10 hours total. I'm having trouble full wrapping my mind around the changes and errors but here is what its showing

a. Time now Starts at "2019-12-31 14:00:00" and ends at "2020-12-31 13:59:00"

b. The date time trim reflects a change to the next day at the "16:00:00" each day.

4. I ran the table back to designer to see if there were any more changes, same table as what is shown in Snowflake from step 3.

Can anyone else try replicating this issue and see if it also happens for them?

Step 2 Results.csv

Step 3 Results.csv

Step 1 Results.xlsx

s_graham · Answer

Im running

Server2022.1.1.30961

s_graham · Answer

still didn't fix it... this truly is the most odd thing. see photos below, first is out of desktop, second is out of gallery. I ran these 2 workflows with connections to snowflake less than five minutes apart from each other, no updates to the data source were performed in the interim.

Deal Close Date = input source raw data

Performed Month = DateTimeTrim([Deal Close Date],'month')

Deal Close Month = DateTimeMonth([Deal Close Date])

Deal Close Day = DateTimeDay([Deal Close Date])

Calcd Close Date = DateTimeTrim([Deal Close Date],'day')

There are other dates in my workflow having this same issue. Basically any DateTime with a Time after 16:17:35 any day of the month is getting bumped back to the next day in gallery. (might be a different cutoff time, this is just the earliest one in my data set)

Also odd to me that gallery and desktop are interpreting the time of day differently for the same record. I cannot stress enough these are identical workflows and connections.

Desktop

Gallery

Any of the gallery engineers have any information on this?