To display date and time from Database to Appian irrespective of user Time zone

Hi All,

We wrote a query to fetch  date from one particular entity from Database, but the output(date) differs based on the user time zone.

For example ,

In Database it shows as 10th June 2021,12:06 AM,

For GMT time zone user it shows as 10th June 2021,12:00 AM as output in expression rule,

For EST time zone user it shows as 9th June ,8:00 PM as output in expression rule.

Can you please help us to sort this, Thanks in Advance.

Regards,

Jansi J

  Discussion posts and replies are publicly visible

  • I think there's two things going on here. First, it looks like the datetime is being converted to a date only at some point in your expression. That's why you're seeing 12:06 AM in the database and 12:00 AM in Appian. Can you share your expression where you're using this datetime value?

    Your original question is about showing the results in a timezone - can you clarify what you want to show? Do you want to show the datetime itself without a time zone? Do you expect the values stored in the database are in a time zone other than GMT Technically the result you're seeing is correct because the database always stores results in GMT and the local time is offset.

    If you do need to do an offset, typically using the local() or gmt() function can get you what you want. For example, I'm currently in EDT - if I run the following expression, it assumes the time provided is in my current time zone and the resulting time is in GMT:

    datetext(
      gmt(
        datetime(2021, 10, 1, 8, 52), 
        "GMT"
      ),
      "M/dd/yyyy h:mm a"
    )