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
DB time values are stored in UTC (essentially the same as GMT but some subtle differences do exist if you look it up); this is, AFAIK, fairly industry-standard, and should be assumed in your application design. Appian makes it trivially easy to convert stored time values back to the local timezone (or a hardcoded one) if/when needed. I'd estimate that very very few use cases are enough of an emergency that they'd warrant hardcoding the DB to store EST times.
Can you provide more details as far as the issue you're running into? Are these searches/bots retrieving DB data in some unusual way? AFAIK datetime-based query values from Appian will autoconvert their values such that you'd get expected results. Sometimes filtering on datetime fields when feeding in only a date value would need a (pretty simple, tbh) conversion to get the "date boundaries" exactly right in the query result - but I know of no cases where this requires the DB to be in EST, nor any where such conversion would actually make anything easier in the long run.
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.
We use Oracle's FROM_TZ in a database view for troubleshooting (the view has both Appian's original UTC timestamps and generated local timestamps). This helps humans troubleshoot status/history tables from the database. I can see how using CONVERT_TZ for a similar use case in MariaDB could be helpful (especially if you prefer using named zones instead of offsets).