CDS – RobertRaaijmakers.nl

In my previous blog I covered the behavior of the date and time fields within the Power App Platform. This mainly focused on the behavior of displaying the data, but what if you must update those fields through the Web API (or CDS). What value do you have to send to get the correct value in the database?

Just a little recap, the following date/time fields are (currently) supported within CDS (Power App Platform):

Date and Time (Time Zone Independent);
Date and Time (User Local);
Date Only (Time Zone Independent);
Date Only (User Local);
Date Only (Date Only).

All the date and time fields in CDS are stored in UTC and when retrieved through the Web API also retrieved in UTC. So, if we want to store the data in CDS we should submit the UTC value. Isn’t that hard is it?

This means that submitting data through the Web API doesn’t take the time zone of the user submitting the data into account. This is very useful as we can determine exactly what date and time we need to submit to the system and we know that the Web API is always giving us the UTC values (please be aware that the DateTime object in some coding languages, like C#, implicitly contain a time zone value which can lead to strange results).

So, to give an example. If we want to have the same “GMT -12” record as in the previous blog post, we should submit the following values to the Web API. This can be done by any user, living in any time zone.

{
	"new_name": "GMT -12 (Web API)",
	"new_dateonlydateonly": "2019-12-18",
	"new_timezoneindependentdateonly": "2019-12-18T00:00:00Z",
	"new_timezoneindependentdateandtime": "2019-12-18T00:00:00Z",
	"new_userlocaldateonly": "2019-12-18T12:00:00Z",
	"new_userlocaldateandtime": "2019-12-18T12:00:00Z"
}

To get the outcome we expect, the values should be submitted as follows depending on the field type and behavior of the field.

Field Type	Behavior	Value to submit
Date and Time	Time Zone Independent	Just enter the date & time you want everyone to see.
Date Only	Time Zone Independent	Just enter the date (& time) you want everyone to see. Note that the time isn’t shown in the native clients (it is weirdly enough returned when retrieved through the web API).
Date Only	User Local	Convert (in code) the user local time to UTC and submit the UTC value. When retrieved, you get the UTC value. When displayed in the native clients Dynamics will convert the UTC value to the local value of the user.
Date and Time	User Local	Convert (in code) the user local time to UTC and submit the UTC value. When retrieved, you get the UTC value. When displayed in the native clients Dynamics will convert the UTC value to the local value of the user.
Date Only	Date Only	Just submit the date in YYYY-mm-dd format.

I hope you can use this information. Please let me know if there are any further questions or unexpected results.

I get a lot of questions regarding the behavior of Date Time and Date fields within Dynamics 365 (and CDS). Since the behavior in retrieval is different between the interface and the API, I decided to write down the behavior differences and show what field types result in what kind of results.

The following date/time fields are supported within the CDS (Power Apps Platform):

Date and Time (Time Zone Independent);
Date and Time (User Local);
Date Only (Time Zone Independent);
Date Only (User Local);
Date Only (Date Only).

Each type is stored differently in the database which also leads to different results when retrieving the records. To test the different behavior, I created an entity with all different date (and time) field types. After that I created a record with a user in that time zone, to really show the effects I used users in GMT -12, GMT -6, GMT -1, GMT, GMT +1, GMT +6, GMT +12. Every record used the same data to enter in the fields:

Date and Time (Time Zone Independent): 18-12-2019 00:00;
Date and Time (User Local): 18-12-2019 00:00;
Date Only (Time Zone Independent): 18-12-2019
Date Only (User Local): 18-12-2019;
Date Only (Date Only): 18-12-2019.

After the records were created, we retrieved the records through the user interface again with users in all different time zones. Below are multiple screenshots of the default behavior through the interface.

Records shown to someone in GMT -12 (International Date Line West)

As we can deduct from the screenshots the interface behaves in the following manner.

Field Type	Behavior	Value
Date and Time	User Local	Time (and date) is different between time zones. Dependent on two factors: in what time zone the record was created and from what time zone the record is accessed.
Date and Time	Time Zone Independent	Independent of whom created it, and who is accessing this value. The value will always show the same date and time.
Date Only	User Local	Date is different between time zones. It behaves the same as “Date and Time, Time Zone Independent” fields with time set to 00:00.
Date Only	Time Zone Independent	Always shows the same date, independent of whom created it and who is accessing the value.
Date Only	Date Only	Always shows the same date, independent of whom created it and who is accessing the value.

This behavior is kind of expected, and all seems fine. But then we need to integrate these fields with other systems through either the Web API or some connectors and then we get some very interesting results.

If we request the same records through the Web API (and thus CDS) we see how the actual data is ‘stored’ and retrieved and that some valuable logic that is handled in the user interface must be handled ourselves.

The retrieval of the records through the Web API is time zone independent. No matter who is requesting the data, it will always return the same values. If we retrieve the same set of records, the following data is returned for all users.

Name	User Local (Date and Time)	Time Zone Independent (Date and Time)	User Local (Date Only)	Time Zone Independent (Date Only)	Date Only (Date Only)
GMT	2019-12-18T00:00:00Z	2019-12-18T00:00:00Z	2019-12-18T00:00:00Z	2019-12-18T00:00:00Z	2019-12-18
GMT +01	2019-12-17T23:00:00Z	2019-12-18T00:00:00Z	2019-12-17T23:00:00Z	2019-12-18T00:00:00Z	2019-12-18
GMT +06	2019-12-17T18:00:00Z	2019-12-18T00:00:00Z	2019-12-17T18:00:00Z	2019-12-18T00:00:00Z	2019-12-18
GMT +12	2019-12-17T12:00:00Z	2019-12-18T00:00:00Z	2019-12-17T12:00:00Z	2019-12-18T00:00:00Z	2019-12-18
GMT -01	2019-12-18T01:00:00Z	2019-12-18T00:00:00Z	2019-12-18T01:00:00Z	2019-12-18T00:00:00Z	2019-12-18
GMT -06	2019-12-18T06:00:00Z	2019-12-18T00:00:00Z	2019-12-18T06:00:00Z	2019-12-18T00:00:00Z	2019-12-18
GMT -12	2019-12-18T12:00:00Z	2019-12-18T00:00:00Z	2019-12-18T12:00:00Z	2019-12-18T00:00:00Z	2019-12-18

As we can deduct from the table above the Web API behaves in the following manner:

Field Type	Behavior	Value
Date and Time	User Local	Time (and date) is different between time zones. Dependent on the time zone the user was in on the moment that the record was created. The data retrieved is the UTC value.
Date and Time	Time Zone Independent	Independent of whom created it, and who is and accessing this value. The value will always show the same date & time.
Date Only	User Local	Date (and time) is different between time zones. It behaves the same as “Date and Time, User Local” fields with time set to 00:00. The data retrieved is the UTC value.
Date Only	Time Zone Independent	Always shows the same date, independent of whom created it and who is accessing the value.
Date Only	Date Only	Always shows the same date (and only returns the date), independent of whom created it and who is accessing the value.

This means that the behavior in displaying data through the Web API (and CDS connectors using the Web API, or any other integration) can have a different outcome if you don’t handle the time zone conversion yourself.

Field Type	Behavior	Shows the same value through API as in web interface
Date and Time	Time Zone Independent	Yes
Date and Time	User Local	No, data retrieval through web API returns UTC value.
Date Only	Date Only	Yes
Date Only	Time Zone Independent	Yes, despite “date only” also returns time stamp in Web API.
Date Only	User Local	No, data retrieval through web API returns UTC value.

This means that only the User Local fields are the biggest challenge in handling through the Web API. But the conversion is quite simple. Since all values are retrieved in UTC the only thing you must do is determine in which time zone the values should be displayed. For this you can either pick one default time zone and enforce this on all the users of the application, or you can retrieve the time zone settings from the user settings through the Web API and apply this to the retrieved date/time field.

I hoped this helped you get the date and time fields sorted. In my next blog I will cover how to create / update those fields through the Web API.

Category: CDS

Updating date and time fields through the Power Platform Web API

Date and time fields within the Power Apps Platform