Currently, we have the database time set as GMT which is four hours away from the Eastern time zone. We are running into issues with Time based search and bots processing of cases for a certain day since the server time is different.
1. Is it possible to update database tiezone to EST? 2. If we updated to EST what will be the impact of Existing application?
Discussion posts and replies are publicly visible
It sounds like the functions querying data are external to Appian, where Appian is designed to store all datetime values as UTC and automatically convert them back to the local timezone for display/usage in Appian. If you update the datetime values manually in the DB to local, in technical terms, your data will be all screwed up.
I implemented this method to convert UTC dates to EST from mssqltips.com and it works great if you rely on EST/EDT always. This creates a function you can wrap your datetime fields in for queries/reporting outside of Appian, such as views, or any direct database administration. However, I do also use it for reporting in Appian where I need to aggregate datetime values by month, day, year to get an accurate integer value into a view to return to Appian. E.g., if you use something in a view such as DATEPART in MSSQL, you can see the difference as:
SELECT DATEPART(month,'2022-10-01 02:00:00.000') --10 SELECT DATEPART(month,dbo.fn_ConvertUTCToLocal('2022-10-01 02:00:00.000')) --9
We are using maria db. so not able to use the solution which is shared in mssqltips. and tried convert_tz function output is coming as null. is there a way to do in maria db.