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

[papolley]

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'

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

'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!

[Mira_Ghaly]

@papolley 

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

[papolley]

@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:

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

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]

@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.

Hope this clears the confusion!

[papolley]

@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:

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

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]

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