Hi,
I have a scenario where my Database is in GMT and I am trying to convert it to EST time by using GMT function gmt(property(local!data,"XXX",{}),"EDT") its working fine during night time but when this function is executed during day time its giving current date-1.
Any solution for this would be really appreciated.
Discussion posts and replies are publicly visible
When you query from database using QueryEntity the datetimes will be converted to loggedInUser default timezone. You don't have to do any convertions.
Thanks for your replay,
My profile Time zone is in EST only and if I use it directly its working fine in day time but in the night time(8PM) it adds 1 day to the current date, that is why I am converting it to EST by using this function gmt(property(local!data,"XXX",{}),"EDT")
What the initial reply here was indicating is that, your data is in GMT in the database, you are retrieving it into local!data at which time Appian is automatically applying the EDT offset. When you then wrap with gmt(), you are adding an additional EST offset / doubling it, pushing it 4 hours into the next day.
To troubleshoot, review a specific time in the DB, then on your interface review the value of property(local!data,"XXX",{})
Thanks for your replay Chris,
If I do not convert to EST(property(local!data,"XXX",{})) its working fine until 8PM but when you make a request after 8PM its giving future date(current date+1)
Hi Jagan,
I don't think that is possible unless you are doing todate() or something on the datatime value retrieved from DB
Hi Chanakya,
I tried using property(local!data,"XXX",{}) and todate(property(local!data,"XXX",{})) both gave future date at 8pm, am I doing something wrong here can you please post the code which I can use to achieve correct date even if I make a request in the night time.
Thanks
That is the main point to missed mentioning. todate() function treats the input as GMT. You should always have your custom expression rule to convert datetime to date.
/*Converts Datetime to date based on passed user. If no user is passed loggedInUser will be considered */ a!localVariables( local!user : if( rule!RWP_isNullOrEmpty(ri!user), loggedInUser(), ri!user ), local!timeZone : usertimezone(local!user), if( rule!RWP_isNullOrEmpty(ri!dateTime), ri!dateTime, todate(local(ri!dateTime, local!timeZone)) )
I tried using the code provided still I am getting one date minus.
The data in DB is
and when try with the code provide it's giving me the below result
We are using the below code as suggested.
load( local!userTimeZone:usertimezone(loggedInUser()), if(rule!APN_isEmpty(ri!dateValue),"",todate(local(ri!dateValue,local!userTimeZone))))
As a side note, I would recommend moving away from the use of load() and with() and migrating toward the newer a!localVariables() function for variable declarations.
To clarify on the date situation here, are you trying to always use the exact date value of what is in the DB, or do you need to add an offset for timezone, which will change the date at certain times of the day?
If you just want a straight date, try:
if( rule!APN_isEmpty(ri!dateValue), "", date( year(ri!dateValue), month(ri!dateValue), day(ri!dateValue) ) )