Time Zone issue

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

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)
    )
)