I am having requirement to give the user a report with different time zone filter to search for the report.
The design I am thinking is to add column in database with different time zone column and user elect the time zone and call the query from that column.
Can anyone suggest the way to save the time from one column to another with different time zone
Discussion posts and replies are publicly visible
Can you expand on your use case a little - are you giving the user a datetime range to enter say, yesterday @ 10 AM to today @ 10 AM, then filtering records where a dateime field lands in that range for the selected timezone? Or otherwise?
I'm not sure yet that additional fields in a SQL view would be the answer, but further thinking will depend on the use case. In SQL you can always apply the DATEADD() function to shift a field's timezone, but it might be more appropriate to apply the shift in Appian something like:
a!queryFilter( field: "dateTimeField", operator: "between", value: { ri!startDateTime+ri!timezoneOffset, ri!endDateTime+ri!timezoneOffset } )
There are two ways to update the time zone for already existing data.
1. From Appian Process while writing to DB or updating in DB
2. From Database stored procedure
If you use Appian to store the timezone date, use local( datetime, [timezone] ) or gmt( datetime, [timezone] ). It depends on wether you want to Subtracts a time zone offset from a given Date and Time or not.
If you want to store the timezone date using database functions, use the stored procedure to update the new column.
For database reference. https://docs.oracle.com/cd/E11882_01/server.112/e10729/ch4datetime.htm#NLSPG238
For your scenario, second option is relevant because you are planning to add new column.