whats the impact of converting GMT to EST Timezone in Database?

Certified Associate Developer

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

Parents
  • 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

Reply
  • 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

Children