DateTime in a plugin in CRM 2011. UTC or local?
-
Tuesday, May 24, 2011 1:25 PM
I'm writing a plugin that needs to filter on some dates. As I understand it CRM stores all dates as UTC on the database and converts to and from local time for the client, depending on the user's location.
My question is, are the dates given in a plugin's target/pre/post entities UTC or local? The fact a plugin runs on the server suggests UTC, but if it runs in the current user's context does that mean they are local?
If they are local, what happens if I run a plugin as the system user?
And if I then use those dates to do a query (query expression or fetch XML), will it 'just work' or do I need to convert?
I'm only interested in CRM 2011 (online).
Thanks (again),
LJ.
All Replies
-
Tuesday, May 24, 2011 5:07 PMLet me try to see if I can help with your questions.
The dates in the plugin's target pre/post entities should be local.My question is, are the dates given in a plugin's target/pre/post entities UTC or local? The fact a plugin runs on the server suggests UTC, but if it runs in the current user's context does that mean they are local?
Not so sure about this one, but it's easy to find out writing a plugin executing under SYSTEM user. It could be UTC, or probably the default timezone that you have specified for the entire application in System Settings, but I am not so sure at this moment. It would be nice if you can report back with your findings.If they are local, what happens if I run a plugin as the system user?
When you do query, CRM will manage the translation of local time transparently, so you shouldn't need to do any conversion.And if I then use those dates to do a query (query expression or fetch XML), will it 'just work' or do I need to convert?
Daniel Cai | http://danielcai.blogspot.com -
Tuesday, May 24, 2011 5:20 PM
Thanks Daniel,
I just did a test. I'm set up in the UK. The user I'm running under has a timezone of GMT. I created a record for an entity which has a date field on it and set the date to 1st May 2011. When I get that date inside the plug-in I get a DateTime value of 30th April 2011 23:00:00.
We are currently on British Summer Time which is GMT+1. That's what made me think the date passed to the plugin is UTC (i.e. local time - 1 = GMT = UTC).
If not, where has my hour gone?
See also this thread: http://social.microsoft.com/Forums/en/crmdevelopment/thread/6bf31ff6-d6eb-4fd9-87f9-6b48790eed59
LJ
-
Tuesday, May 24, 2011 5:33 PMThis rings a bell to me that it could be a platform bug related to daylight saving. You may try to change a CRM user locale setting to a timezone that doesn't have daylight saving such as China (Beijing) and see if you are still losing one hour as you do with GMT.
Daniel Cai | http://danielcai.blogspot.com -
Tuesday, May 24, 2011 9:53 PM
Thanks again Daniel.
I did some more testing as you suggested and changed the user's timezone to Bejing (GMT+8). When I entered a date of 1st May 2011 the plug-in received 30th April 16:00:00 which is correct for UTC/GMT but not for UK time. Our daylight saving (British Summer Time) started on the last Sunday in March.
I think my testing is made harder because I am in the UK and GMT=UTC so it's hard to tell whether it is a DST issue or whether the plugin is actually receiving a UTC date.
Anyone anywhere else in the world fancy doing some testing? (preferably using 2011 IFD/online to avoid confusing things)
In the CRM options you can only set timezone and not location or country. Presumably it's possible that countries on the same time zone could have different times due to differing rules about daylight saving? If so then CRM can't possibly handle DST correctly.
Of course there is also the issue of not truly being able to specify a (local) date only. If I really don't care about the time part then I would expect if a user entered the date of (say) 1st May 2011 for it to always display as the 1st of May regardless of where I was looking at it from. Because of the UTC conversion it could actually display as 30th April. If I was running reports and filtering on date then the reports would look different depending on which country I'm in.
All a bit worrying ...
-
Tuesday, May 24, 2011 10:05 PMAre you trying to compare the datetime value in db with what you have seen in CRM interface?
Daniel Cai | http://danielcai.blogspot.com -
Tuesday, May 24, 2011 10:12 PM
Daniel my current problem is that a date field (which shouldn't care about time) is either UTC or an hour out (and thus a different date) in my plug-in, whether the user is set to London (GMT) or Bejing (GMT+8).
So I'm trying to establish whether there is a bug in DST handling as you suggested or whether plugins in fact get UTC values not local (contrary to what you said earlier).
Ignore the rest of my rant for now.
Thanks, I do appreciate you taking the time for this.
-
Wednesday, May 25, 2011 10:51 AM
Hi,
It's in UTC format.
Thank you.
JayshriP
- Marked As Answer by Laughing John Thursday, May 26, 2011 10:54 AM
-
Wednesday, May 25, 2011 3:23 PM
Sorry guys, I have to admit that I was a little misleading when I asked if you were trying to read datetime value from db.
If CRM entity datetime attribute values returned in plugin are indeed in UTC, I am relatively positive that it's a bug with CRM 2011.
I don't have an environment handy at this moment, I will do some experiment when I get a chance.
Daniel Cai | http://danielcai.blogspot.com -
Thursday, May 26, 2011 5:11 AM
I have just written my first CRM 2011 plugin and confirmed that the datetime attribute values obtained using target entity's Attributes property are indeed in UTC. Sorry, I was using my CRM 4.0 knowledge to respond to your question. I was too confident that MSCRM won't change the way dealing with CRM datetime after the upgrade, since I knew that CRM4 had got everything correct with regard to datetime value handling in plugin. However, for a reason, MSCRM team has decided to do it a little differently in CRM 2011.
A quick summary of what I have found so far.
- When you retrieve datetime values using CRM entity, you will get the values in UTC
- When you assign a CRM attribute to a datetime value, CRM will take the value based on the datetime value's Kind property. If it's a local time, CRM will translate it into UTC time before saving to CRM database. If it's already an UTC time, then CRM will save it as it is.
With that said, you will have to convert between UTC and local time if you ever need to manipulate one that's retrieved from CRM entity. With the datetime obtained through target entity in plugin, you can check its .Kind property, if you detect Utc, you will need to convert to local time before you should make change.
To convert between UTC and local time, I believe you can use UtcTimeFromLocalTimeRequest and LocalTimeFromUtcTimeRequest messages. There might be better way, since each of the requests will require a service call to the server.
I tested using GMT-8 with currently DST going on, I see the offset is 7 hours, which is correct to me. I can see the datetime value has .Kind of "UTC", and it is my local time + 7 hours
I apologize for any confusions that may have been caused by my previous responses.
Thanks,
Daniel Cai | http://danielcai.blogspot.com
- Marked As Answer by Laughing John Thursday, May 26, 2011 10:54 AM
- Edited by Daniel CaiMVP Friday, May 27, 2011 2:28 AM
-
Thursday, May 26, 2011 8:30 AM
Guys, thanks so much, especially you Daniel for taking the time to actually research this. Above and beyond the call of duty!
Daniel, does this mean that if a plugin takes a datetime from the entity that triggered it and creates/updates another entity with that date, the code needs to do a conversion?
This is pretty important and if it's not a bug then it certainly should be thoroughly documented somewhere! If what you say about CRM4 is also correct then it could be important for anyone upgrading their plugins to work with 2011.
The next question is if I do a query using either fetchxml or a query expression within the plugin what sort of date is that expecting? Based on our findings I would imagine it also expects a local date.
Anyone from the CRM team at Microsoft reading this?
- Edited by Laughing John Thursday, May 26, 2011 9:57 AM
-
Thursday, May 26, 2011 9:06 AM
LJ,
Are you aware that when you query with FetchXml and you use "on-or-before" is stead of "le" you get a different result. "on-or-before" uses the Users local time settings.
<xs:enumeration value="eq" />
<xs:enumeration value="neq" />
<xs:enumeration value="ne" />
<xs:enumeration value="gt" />
<xs:enumeration value="ge" />
<xs:enumeration value="le" />
<xs:enumeration value="lt" />
<xs:enumeration value="like" />
<xs:enumeration value="not-like" />
<xs:enumeration value="in" />
<xs:enumeration value="not-in" />
<xs:enumeration value="between" />
<xs:enumeration value="not-between" />
<xs:enumeration value="null" />
<xs:enumeration value="not-null" />
<xs:enumeration value="yesterday" />
<xs:enumeration value="today" />
<xs:enumeration value="tomorrow" />
<xs:enumeration value="last-seven-days" />
<xs:enumeration value="next-seven-days" />
<xs:enumeration value="last-week" />
<xs:enumeration value="this-week" />
<xs:enumeration value="next-week" />
<xs:enumeration value="last-month" />
<xs:enumeration value="this-month" />
<xs:enumeration value="next-month" />
<xs:enumeration value="on" />
<xs:enumeration value="on-or-before" />
<xs:enumeration value="on-or-after" />
<xs:enumeration value="last-year" />
<xs:enumeration value="this-year" />
<xs:enumeration value="next-year" />
<xs:enumeration value="last-x-hours" />
<xs:enumeration value="next-x-hours" />
<xs:enumeration value="last-x-days" />
<xs:enumeration value="next-x-days" />
<xs:enumeration value="last-x-weeks" />
<xs:enumeration value="next-x-weeks" />
<xs:enumeration value="last-x-months" />
<xs:enumeration value="next-x-months" />
<xs:enumeration value="olderthan-x-months" />
<xs:enumeration value="last-x-years" />
<xs:enumeration value="next-x-years" />
<xs:enumeration value="eq-userid" />
<xs:enumeration value="ne-userid" />
<xs:enumeration value="eq-userteams" />
<xs:enumeration value="eq-businessid" />
<xs:enumeration value="ne-businessid" />
<xs:enumeration value="eq-userlanguage" />
<xs:enumeration value="this-fiscal-year" />
<xs:enumeration value="this-fiscal-period" />
<xs:enumeration value="next-fiscal-year" />
<xs:enumeration value="next-fiscal-period" />
<xs:enumeration value="last-fiscal-year" />
<xs:enumeration value="last-fiscal-period" />
<xs:enumeration value="last-x-fiscal-years" />
<xs:enumeration value="last-x-fiscal-periods" />
<xs:enumeration value="next-x-fiscal-years" />
<xs:enumeration value="next-x-fiscal-periods" />
<xs:enumeration value="in-fiscal-year" />
<xs:enumeration value="in-fiscal-period" />
<xs:enumeration value="in-fiscal-period-and-year" />
<xs:enumeration value="in-or-before-fiscal-period-and-year" />
<xs:enumeration value="in-or-after-fiscal-period-and-year" />
<xs:enumeration value="begins-with" />
<xs:enumeration value="not-begin-with" />
<xs:enumeration value="ends-with" />
<xs:enumeration value="not-end-with" />
- Marked As Answer by Laughing John Thursday, May 26, 2011 10:54 AM
-
Thursday, May 26, 2011 9:31 AM
Jan,
No I wasn't aware of that Jan. Thanks for pointing it out!
LJ.
-
Thursday, May 26, 2011 9:37 AM
In one of my previous posts I went a bit "ranty" because I am finding more problems with CRM's date handling. I just wanted to highlight one of the other issues I was trying to explain in case someone else comes across it. In a nutshell the problem is that CRM does not handle Date-Only fields properly. There is an option to only display the date, but that date is still treated as a DateTime which may cause issues if you really are only concerned with the (local) date.
Here is a contrived example:
User1 is GMT+8, User2 is GMT-5, User3 is GMT.
There is an entity called trade which has a date field displayed as "date only".
User1 enters a trade on 1st May 2011. It is stored to the DB as 30th April 2011 16:00:00 (UTC)
User2 enters a trade on 1st May 2011. It is stored to the DB as 1st May 2011 05:00 (UTC)
User3 runs a report to total all trades on 1st May 2011. Report does not include User1's trade. The numbers are now wrong.Microsoft are aware of this issue as can be seen from this Microsoft Connect entry:
If you come across this problem please vote!
-
Thursday, May 26, 2011 10:51 AM
OK, I'm feeling a bit dumb now because I'd forgotten about the Kind property on a DateTime. Displaying that absolutely confirms that date/times received by a plugin are UTC.
Additionally I think I have proven that when you create a second entity from a plugin and use the datetime value on that, CRM works it all out correctly. In other words the date on the second entity displays correctly.
An example:
- User is GMT+8
- Enter a date of 1st May 2011 on entity1
- Plugin receives a value of 30th April 2011 16:00:00 with a Kind of UTC. So it's a UTC date.
- Create entity2 (different type) using the date from entity1.
- Entity2 displays correctly as 1st May 2011.
Presumably CRM looks at the Kind value and only does the UTC conversion if it isn't already done.
So hopefully my only remaining questions are:
- When you use a query expression from a plugin in CRM is it clever enough to do that conversion or does it expect either local or UTC?
- How does it work for queries using FetchXml. As Jan pointed out using the On, On or Before, On or After operators uses the local times. I'm wondering what happens if you use the standard operators (lessthan, equal etc.) and format the date string into one that either includes the timezone (like "o" rountrip) or is UTC (like "r" RFC1123 ).
I think the "date only" problem is still valid however!
Thanks to you all for your help!
LJ
- Marked As Answer by Laughing John Thursday, May 26, 2011 10:54 AM
-
Friday, May 27, 2011 2:22 AM
After a little more investigation, I have been a little relieved to realize that CRM 2011 has actually tried to respect the Kind property of .NET DateTime object. When you provide a UTC datetime, it will be saved as it is. But when you provide a Local time, it will be translated to UTC based on the user's timezone settings. This starts to make sense to me now. I have updated my previous response in order to reflect this finding.
When it comes to query, I believe it will depend on how you pass the datetime value to the platform.
Daniel Cai | http://danielcai.blogspot.com
-
Friday, May 27, 2011 8:30 AM
Thanks again Daniel, I raised the issue on Microsoft Connect asking them to dedicate some space in the SDK documentation to explain the way CRM handles dates more precisely. I'm obviously not the only one it's confusing!
The link is here: http://connect.microsoft.com/dynamicssuggestions/feedback/details/670739/update-documentation-to-clarify-how-crm-deals-with-dates-time-zones-dst-utc-etc if you fancy voting!
-
Thursday, June 21, 2012 1:53 PM
...
An example:
- User is GMT+8
- Enter a date of 1st May 2011 on entity1
- Plugin receives a value of 30th April 2011 16:00:00 with a Kind of UTC. So it's a UTC date.
- Create entity2 (different type) using the date from entity1.
- Entity2 displays correctly as 1st May 2011.
...
For my dates routine in WF assemblies works DateTime.ToLocalTime() method. So in your examle there must be on additional step '3a':
DateTime userEnteredDateTime = recievedInPluginDateTime.ToLocalTime();
After that you can calculate what do you want. And you do not need any special conversion to UTC on new dates (based on userEnteredDateTime) in plugin while saving it.
- Edited by Vladislav Osmanov Thursday, June 21, 2012 1:54 PM code highlight
- Proposed As Answer by Vladislav Osmanov Friday, June 22, 2012 5:46 AM
