cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
papolley
Microsoft
Microsoft

Is it possible to change ModifiedOn to use UTC Standard Time instead of user local time?

Hello all,

 

I have a possibly insane question. Is it possible to change the properties of the system defined column 'modifiedon' on a table to be time zone independent?

When I try to modify in the UI, the option is greyed out and preset to 'User Local'

papolley_0-1639010367107.png

 

And when I try to edit using the xrm toolbox, I get an error here:

papolley_1-1639010524021.png

'cannot modify the behavior if CanModifyBehavior is set to false for OOB date time attributes of a custom entity'

Maybe this is a setting I could find somewhere in admin settings? I can't seem to find anything on first glance, though.

 

A secondary question would be: is there a way to make another column with the same auto-updating functionality that is set to 'Time Zone Independent'? Or any way to work around this? Maybe through business rules or flows or something? My first thought would be to create a flow that would trigger on row modification that would set a custom column datetime to the current utc timestamp, but that seems sorta dumb and bad?

 

Any help would be greatly appreciated. Thank you!

5 REPLIES 5
Mira_Ghaly
Dual Super User
Dual Super User

@papolley 

I would suggest using a calculated field of Type Date Time and Behaviours Time Zone Independent and Set it to Modified On.

Mira_Ghaly_0-1639013097269.png

 

If this post helps you with your problem, please mark your as Accepted solution.If you like my response, please give it a Thumbs Up.

Blog: here

@Mira_Ghaly Hello! Thank you for responding.

 

I think I did as you suggested, but I'm not seeing a UTC time output in the calculated field I think?

I added a new column here:

papolley_0-1639064128893.png

papolley_1-1639064172797.png

 

However, when I try to look at the data output, I see this:

papolley_2-1639064309565.png

 

I may be misunderstanding the output and/or how powerapps api messes with data, but it looks like the calculation isn't taking into account a time zone?

 

Thank you for the help! Sorry for the trouble.

Mira_Ghaly
Dual Super User
Dual Super User

@papolley 

TimeZone Independent behaviour, It means they appear the same on the UI and the SDK Which means UTC

 

Unlike Local Time:

 

The field values are displayed in the current user’s local time.
- In Web services (SDK), these values are returned using a common UTC time zone format.

 

So the modified on date is saved in the database as UTC but displayed as Local Time Zone.

So in your case you are querying the database so both fields on the database level should be equal and in UTC format but on the UI this is how they look.

Mira_Ghaly_0-1639104307132.png

Mira_Ghaly_1-1639104512481.png

Hope this clears the confusion!

 

 

If this post helps you with your problem, please mark your as Accepted solution.If you like my response, please give it a Thumbs Up.

Blog: here
papolley
Microsoft
Microsoft

@Mira_Ghaly 

 

I understand what you're saying I think but I'm unsure if this behavior is what I am seeing in practice.

 

For context, the environment I'm working is in localized to USA west coast time (utc-8) and that is the time zone I am working from. I think I am seeing the user local time in these direct database queries, not UTC. I'm a little unsure how to show this through screenshots but I'll do my best here.

 

It is ~11:42 UTC-8 == 19:42 UTC time when I save edits to my table for this example. I have just saved a record at this time:

papolley_0-1639165467350.png

 

When I query my database through python on azure databricks immediately after, I see this:

papolley_1-1639165663765.png

I may be misunderstanding what I am seeing here or something about the nature of the timestamp/data is eluding me, but it looks to me like both timestamps are in user localized time, aka UTC-8. I think there may be a bug here?

 

I have figured out a painful yet functional workaround in my specific use case here (comparing column to column between a diff table and this table here), but I think there may be some problem here somewhere in the power platform itself?

 

Regardless, thank you for helping me understand better here! sorry to continuously bother.

GuidoPreite
Super User
Super User

hopefully I don't add confusion here.

modifiedon is an "internal" field, when I write "internal" in this case I mean that is created by the system and the behavior can't be changed.

It will store the date when the record has been modified, you can't put a custom value.

Time zones are complex but to just define a point in time is not. Let's start to the fact that all the dates inside Dataverse are stored as UTC, so assume that your event happens at 8 AM UTC time.

How and to WHO is displayed that data is the problem.

If my user is configured with a time zone as UTC+2, inside a model-driven app I would see 10 AM as the modifiedon, because that is the exact time happened, but if I change the same user to use UTC and I refresh the form I would see 8 AM.

 

Now from your latest screenshot you are not checking the data using a model-driven app or the SDK, but you should see there the UTC value and not your local time.

If you still have the problem, check the current UTC time, create a record and query again with the tool you are using, and verify that the value returned has the UTC value or not.

hope it helps

 

 

Helpful resources

Announcements
Power Apps News & Annoucements carousel

Power Apps News & Announcements

Keep up to date with current events and community announcements in the Power Apps community.

Community Call Conversations

Introducing the Community Calls Conversations

A great place where you can stay up to date with community calls and interact with the speakers.

Power Apps Community Blog Carousel

Power Apps Community Blog

Check out the latest Community Blog from the community!

Users online (5,718)