Hi, the grouping is not working as expected in bar chart when i am using a!recorddata. I am working on PST time. So, any record which is created after 4 PM PST is grouped as the next day. Let's say I have 2 records, one at Feb 12, 3 PM PST and another at Feb 12, 5 PM PST. When I apply a filter for Feb 12, the bar chart actually shows me data of two dates, Feb 12 and Feb 13. The record which is created at 5PM PST is grouped to Feb 13. It is most likely due to the time difference(8 hours) between gmt and pst, as I can clearly observe any record after 4 PM PST is grouped to the next day. Is this behaviour expected? Please suggest me a solution.
Discussion posts and replies are publicly visible
Yes, expected: Appian converts datetimes from UTC to your local timezone.Add custom record field pst_date: datevalue(local(created_datetime, "America/Los_Angeles")); use that Date field for primaryGrouping with interval: DATE_TEXT. Groups by PST calendar day.https://docs.appian.com/suite/help/26.1/Chart_Configuration_Using_Records.html#apply-time-intervals
Hi, Thanks for the solution. It worked. Btw, I still feel Appian should not have done the conversion during the grouping. Even if it did, I think it should have done -8 hours right and keeping it on the same day? rather than doing a +8 and changing it to next day.
And coming back to the conversation, when I query the data, it converts to PST(my local time) and gives as expected, meaning when I add a record at 5 PM PST, it saves in gmt in db and returns as 5 PM PST. The issue happens only when it groups with a datetime field. I also tried with the createdOn datetime field with the hour interval and it grouped to the same day, meaning I can see the records after 4 PM for the same day. It was only when I used the interval DATE_TEXT, it started acting funny and grouping the records after 4 PM to the next day.
++ adding the GOATS Stefan Helzle Mike Schmitt for their perspectives
Interesting conundrum honestly - I assume you're originally referring to query-time aggregation, no? So the grouping puts the boundary at GMT/UST (which i'd basically expect) but it doesn't end up looking correct or intuitive for the end user (or even the designer really) since the data at the boundaries between groups ends up being intuitively wrong. I'm not sure what feasible solution there might be for this, given the basic nature of the aggregation grouping functionality available to us.
Stefan might have a good idea that I haven't thought of, but if it were critical to me I'd raise it with Appian to see if they happen to have any supported solution.
The "traditional" (CDT backed) approach MIGHT be to create a view with "alternate" date/time columns that carry "converted" values of the date/times causing grouping into the correct groups after time zone adjustment. I suppose it's possible you could create custom columns in your record type that do a similar preemptive conversion, but I haven't tried such a thing.