cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
dungar
Helper III
Helper III

Date/Time is wrong

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.

1 ACCEPTED SOLUTION

Accepted Solutions
poweractivate
Most Valuable Professional
Most Valuable Professional

@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

 

View solution in original post

2 REPLIES 2
poweractivate
Most Valuable Professional
Most Valuable Professional

@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

 

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.

Helpful resources

Announcements

Celebrating the May Super User of the Month: Laurens Martens

  @LaurensM  is an exceptional contributor to the Power Platform Community. Super Users like Laurens inspire others through their example, encouragement, and active participation. We are excited to celebrated Laurens as our Super User of the Month for May 2024.   Consistent Engagement:  He consistently engages with the community by answering forum questions, sharing insights, and providing solutions. Laurens dedication helps other users find answers and overcome challenges.   Community Expertise: As a Super User, Laurens plays a crucial role in maintaining a knowledge sharing environment. Always ensuring a positive experience for everyone.   Leadership: He shares valuable insights on community growth, engagement, and future trends. Their contributions help shape the Power Platform Community.   Congratulations, Laurens Martens, for your outstanding work! Keep inspiring others and making a difference in the community!   Keep up the fantastic work!        

Check out the Copilot Studio Cookbook today!

We are excited to announce our new Copilot Cookbook Gallery in the Copilot Studio Community. We can't wait for you to share your expertise and your experience!    Join us for an amazing opportunity where you'll be one of the first to contribute to the Copilot Cookbook—your ultimate guide to mastering Microsoft Copilot. Whether you're seeking inspiration or grappling with a challenge while crafting apps, you probably already know that Copilot Cookbook is your reliable assistant, offering a wealth of tips and tricks at your fingertips--and we want you to add your expertise. What can you "cook" up?   Click this link to get started: https://aka.ms/CS_Copilot_Cookbook_Gallery   Don't miss out on this exclusive opportunity to be one of the first in the Community to share your app creation journey with Copilot. We'll be announcing a Cookbook Challenge very soon and want to make sure you one of the first "cooks" in the kitchen.   Don't miss your moment--start submitting in the Copilot Cookbook Gallery today!     Thank you,  Engagement Team

Announcing Power Apps Copilot Cookbook Gallery

We are excited to share that the all-new Copilot Cookbook Gallery for Power Apps is now available in the Power Apps Community, full of tips and tricks on how to best use Microsoft Copilot as you develop and create in Power Apps. The new Copilot Cookbook is your go-to resource when you need inspiration--or when you're stuck--and aren't sure how to best partner with Copilot while creating apps.   Whether you're looking for the best prompts or just want to know about responsible AI use, visit Copilot Cookbook for regular updates you can rely on--while also serving up some of your greatest tips and tricks for the Community. Check Out the new Copilot Cookbook for Power Apps today: Copilot Cookbook - Power Platform Community.  We can't wait to see what you "cook" up!      

Tuesday Tip | How to Report Spam in Our Community

It's time for another TUESDAY TIPS, your weekly connection with the most insightful tips and tricks that empower both newcomers and veterans in the Power Platform Community! Every Tuesday, we bring you a curated selection of the finest advice, distilled from the resources and tools in the Community. Whether you’re a seasoned member or just getting started, Tuesday Tips are the perfect compass guiding you across the dynamic landscape of the Power Platform Community.   As our community family expands each week, we revisit our essential tools, tips, and tricks to ensure you’re well-versed in the community’s pulse. Keep an eye on the News & Announcements for your weekly Tuesday Tips—you never know what you may learn!   Today's Tip: How to Report Spam in Our Community We strive to maintain a professional and helpful community, and part of that effort involves keeping our platform free of spam. If you encounter a post that you believe is spam, please follow these steps to report it: Locate the Post: Find the post in question within the community.Kebab Menu: Click on the "Kebab" menu | 3 Dots, on the top right of the post.Report Inappropriate Content: Select "Report Inappropriate Content" from the menu.Submit Report: Fill out any necessary details on the form and submit your report.   Our community team will review the report and take appropriate action to ensure our community remains a valuable resource for everyone.   Thank you for helping us keep the community clean and useful!

Community Roundup: A Look Back at Our Last 10 Tuesday Tips

As we continue to grow and learn together, it's important to reflect on the valuable insights we've shared. For today's #TuesdayTip, we're excited to take a moment to look back at the last 10 tips we've shared in case you missed any or want to revisit them. Thanks for your incredible support for this series--we're so glad it was able to help so many of you navigate your community experience!   Getting Started in the Community An overview of everything you need to know about navigating the community on one page!  Community Links: ○ Power Apps ○ Power Automate  ○ Power Pages  ○ Copilot Studio    Community Ranks and YOU Have you ever wondered how your fellow community members ascend the ranks within our community? We explain everything about ranks and how to achieve points so you can climb up in the rankings! Community Links: ○ Power Apps ○ Power Automate  ○ Power Pages  ○ Copilot Studio    Powering Up Your Community Profile Your Community User Profile is how the Community knows you--so it's essential that it works the way you need it to! From changing your username to updating contact information, this Knowledge Base Article is your best resource for powering up your profile. Community Links: ○ Power Apps ○ Power Automate  ○ Power Pages  ○ Copilot Studio    Community Blogs--A Great Place to Start There's so much you'll discover in the Community Blogs, and we hope you'll check them out today!  Community Links: ○ Power Apps ○ Power Automate  ○ Power Pages  ○ Copilot Studio    Unlocking Community Achievements and Earning Badges Across the Communities, you'll see badges on users profile that recognize and reward their engagement and contributions. Check out some details on Community badges--and find out more in the detailed link at the end of the article! Community Links: ○ Power Apps  ○ Power Automate  ○ Power Pages  ○ Copilot Studio    Blogging in the Community Interested in blogging? Everything you need to know on writing blogs in our four communities! Get started blogging across the Power Platform communities today! Community Links: ○ Power Apps  ○ Power Automate  ○ Power Pages  ○ Copilot Studio   Subscriptions & Notifications We don't want you to miss a thing in the community! Read all about how to subscribe to sections of our forums and how to setup your notifications! Community Links: ○ Power Apps  ○ Power Automate  ○ Power Pages  ○ Copilot Studio   Getting Started with Private Messages & Macros Do you want to enhance your communication in the Community and streamline your interactions? One of the best ways to do this is to ensure you are using Private Messaging--and the ever-handy macros that are available to you as a Community member! Community Links: ○ Power Apps  ○ Power Automate  ○ Power Pages  ○ Copilot Studio   Community User Groups Learn everything about being part of, starting, or leading a User Group in the Power Platform Community. Community Links: ○ Power Apps  ○ Power Automate  ○ Power Pages  ○ Copilot Studio   Update Your Community Profile Today! Keep your community profile up to date which is essential for staying connected and engaged with the community. Community Links: ○ Power Apps  ○ Power Automate  ○ Power Pages  ○ Copilot Studio   Thank you for being an integral part of our journey.   Here's to many more Tuesday Tips as we pave the way for a brighter, more connected future! As always, watch the News & Announcements for the next set of tips, coming soon!

Hear what's next for the Power Up Program

Hear from Principal Program Manager, Dimpi Gandhi, to discover the latest enhancements to the Microsoft #PowerUpProgram, including a new accelerated video-based curriculum crafted with the expertise of Microsoft MVPs, Rory Neary and Charlie Phipps-Bennett. If you’d like to hear what’s coming next, click the link below to sign up today! https://aka.ms/PowerUp  

Top Solution Authors
Top Kudoed Authors
Users online (5,950)