I've been having headaches with dates in Appian and I think it has to do with the way appian handles timezones.
Unless I'm mistaken, appian is supposed to record dates in GMT then then later read them in whatever local time zone is appropriate. What I'm finding is that it records in the local time zone, then later reads it as though it were GMT and adjusts to the local time zone, resulting in incorrect dates.
The more frustrating thing about this is how I access/display the date affects what gets shown.
For example if I use a date picker, appian records the date as 4 hours earlier. Thus 8/8/2025 will be recorded in the db as 8/7/2025 8:00 pm EDT
If I display the date in a text field, I'll get 8/7/2025 8:00 pm EDT, but if I put it through any kind of function such as text() to format what it looks like it'll come out as 8/8/2025.
What's worse is I imported a csv of a bunch of legacy data and the dates were all converted to date/time and had the 4 hours subtracted when recorded.
How can I keep this from happening?
Discussion posts and replies are publicly visible
The functions local() and gmt() are your friends. It's tricky to remember how and when you can/want/need to use each, but with practice you will get used to it.
The thing is they don't change the time zone, just the time, which makes things sticky. Say it's 4:00 now. I use the now() function and appian returns 4pm EDT. If I use the local() function, it gives me 12pm EDT. wrapping now() in the GMT function gives me 8pm EDT. They all say EDT regardless of the actual time zone that is being recorded. I want the time zone in the db to at least be accurate
You have to remember that some date/timestamps are stamped with a timezone and some are not. A lot of the confusion comes into play when dealing with an inherently non-timezone-stamped time value (like "today()") but then dealing with that in the context of a time rather than as a date, when it inherently gets timestamped - and, of course, Appian treats all timestamps behind-the-scenes as GMT/UST, other than how they're displayed to the end user - this is where things like GMT and LOCAL come into play.
Marco Tacca said:I want the time zone in the db to at least be accurate
The "time zone in the db" is always accurate, as it's always GMT/UST. What you must straighten out is what the end user sees and what gets saved/retrieved, and often, this goes hand-in-hand with the developer's understanding thereof.
Mike Schmitt said:The "time zone in the db" is always accurate, as it's always GMT/UST. What you must straighten out is what the end user sees and what gets saved/retrieved, and often, this goes hand-in-hand with the developer's understanding thereof.
The frustration over here is that it ISN'T always GMT in the DB. If it was doing that, I wouldn't be having all these headaches with time. I'm getting either EST or EDT on the DB.
Marco Tacca said:I'm getting either EST or EDT on the DB
How is that happening? If you use any standard funcitonality to store a standard timestamp into the DB, the datetime value should be stored as UST, unless you first applied an incorrect conversion, or something else unusual has happened to it first.
Edit: I now see your followup comment to Stefan explaining that the DB is not in cloud and hardcoded to be EST-centric. In that case I'm not sure what to do other than to make sure all in-Appian settings are set such that it expects everything at all to be in EST.