Date/Time is wrong

[dungar]

I have an app where a user can make an entry, and we'd like to log the date and time it occurred at in a AZURE MS SQL database table.

I am well aware of the time zone issues, so this column is a date time offeset, and the submission code is this -

DateAdd(
Now(),
-TimeZoneOffset(Now()),
Minutes
)

In actual testing, i'm getting variances of minutes (7+) to 1 hour for back to back submissions.

I constantly struggle with date/time in powerapps, so is there an easy way to say "I will only ever care about THIS time" and hard-coded it to PST or something.  It is insanely frustrating how inaccurate it seems to be.

[poweractivate · Accepted solution]

@dungar 

For higher consistency of the time, maybe it is better to:

1. If possible, and if supported for your scenario, create column in SQL Azure table using current_timestamp so it uses the SQL server side timestamp, and use this column instead and not worry about the timestamp at all from Power Apps side.

2. If #1 is not possible, avoid the TimeZoneOffset and just use the Now() directly and log UTC time consistently, if it is possible for your scenario. However, each device using the app might have different local times set - with variance of up to a few seconds, to even a few minutes - or even more of a variance in some cases - so you may still get values you don't want even if you log in UTC consistently - because your problem comes from relying on the client's time in the first place in a scenario like this.

The reason you may have a problem with the above is those Power Apps functions take it from the local computer's time. Two computers may have different time from up to few seconds to even a few minutes, or even an hour or more off of the expected time depending on the date and time set on the local computer and how it was set. If this bothers you, I think you may want the timestamp generated from MS SQL server instead or from one centralized place rather than the actual client which submitted the time. The client may well have a time set to something way off of what the actual current time is.

Worse yet, suppose 10 devices using your app that uses the Power Apps Now() function. Whether or not you convert to local or keep it as UTC, the time may be off by seconds, minutes, hours, or even days or weeks - it is the date and time of multiple different clients after all and they are probably not going to be the same values. So I think the time should be somehow kept track of from one place - such as leveraging the current_timestamp functionality of SQL server itself, for example

[dungar]

Thanks for the guidance.  I've stayed away from a sql side solution for too long and I really should have investigated that sooner.  I think i just got used to client side handling, and expected it to be much simpler in powerapps(i really am surprised there seems to be no way to just say "This app will always be in THIS time zone", but yeah if it's pulling the local system time that makes sense), and have been wrestling with that ever since.

That said for future readers who maybe don't have access to sever side solutions, i found that the issue was that since I called Now() directly in the data card update property of the form, it appeared to calculate that ONCE, when the page was loaded. 

So if a user would go to the page, take their time filling out the form, get distracted, and then submit that form, then submit the second form (which auto populates based on the first and only takes moments), you'd get huge delta's between the two submission times causing all sorts of problems (and boooy do users love to just leave open their screen to try and be efficient).

Instead on the submission click, I added a Set(varCurrentTime, Now()); line and then just update that.  An obvious workaround in hindsight but i was assuming now would be calculated on the submission, not on the page load (which again thinking about it more I can see how that could make sense, but man some of this stuff feels like a major beginner trap)

Edit-

Thinking a bit more, i think the reason i DONT use a sql side timestamp was because it was causing havoc with my filter logic when i'd pull the data back into the app.  I need end users to be able to see older records filtered by day, and if i recall right you basically need a datetimeoffset for it to play even remotely nice, or else you're looking at another batch of workarounds.  I could be wrong, but i guess i'll have to investigate that whole avenue more...or perhaps just do both.