The Insanity of AX Timezones and DateTimeUtil
I am working on an implementation that imports employee data. Some of the data is employment related dates. Many of these dates use the utcDateTime type. When importing with a date I found dates to be off by one day. I figured it was related to the timezone so I have done some experimentation and found some shocking results.The first issue is the msdn documentation seems to be misleading if not incorrect. This document states:
When you are programming with dates, Best Practices are ... Use DateTimeUtil::getSystemDateTime instead of systemDateGet or today... Only DateTimeUtil::getSystemDateTime compensates for the time zone of the user.However, when I execute the following code on November 2nd at 3:26 PM:
I am surprised to get this:
utcDateTime testDateTime;
testDateTime = DateTimeUtil::getSystemDateTime();
info(
strFmt(
'testDateTime is %1 using timezone %2.',
testDateTime,
enum2str(
DateTimeUtil::getOriginatingTimeZone(
testDateTime)
)
)
);
testDateTime is 11/2/2012 08:26:01 pm using timezone (GMT) Casablanca, Monrovia, Reykjavik.The dateTimeUtils::getSystemDateTime did in fact compensate for the time zone of the user but not as I expected. I expected it to return a utcDateTime with the time system time in my timezone. I can live with this by simply applying my timezone. (I'll cover how to do this and why it doesn't work a little later.)
However, my issue was with importing times in a CSV file. So let's try this:
testDateTime = str2datetime('11/2/2012 15:26:00',213);What I get is predictable:
testDateTime is 11/2/2012 03:26:00 pm using timezone (GMT) Casablanca, Monrovia, Reykjavik.Since I didn't specify a timezone, GMT is assumed for the time I created. So let's apply a timezone offset to this time. My timezone is CST which is GMT minus 6 hours. The timezone enum for this is Timezone::GMTMINUS0600CENTRALTIME.
testDateTime = DateTimeUtil::applyTimeZoneOffset(testDateTime,I can't figure out what how I got this:
DateTimeUtil::getUserPreferredTimeZone());
testDateTime is 11/2/2012 10:26:00 am using timezone (GMT) Casablanca, Monrovia, Reykjavik.There are two serious things wrong with this. First, the time zone for the utcDateTime is still GMT. How can I possibly pass that data around and know if need to apply the timezone or not. Second, if you do the math you will see this is 5 hours different not 6.
There is another way to create a utcDateTime and apply a time zone offset so I thought I would try that. The DateTimeUtil class has a static method that will create a utcDateTime for you. The DateTimeUtil::newDateTime function takes the parameters. The third (optional) parameter is a Timezone enum value. The other two are a date and a time. I figure I can read the date string from the import, add a constant time value and apply the timezone and get a utcDateTime that is correct.
Here is the code:
DateTimeUtil::newDateTime(Maybe it was unreasonable for me to expect that I could create a utcDateTime value of November 2nd, 3:26 PM CST with that code, but that is what I expected. Instead I got this:
str2Date('11/2/2012', 213),
str2time('15:26:00'),
Timezone::GMTMINUS0600CENTRALTIME);
testDateTime is 11/2/2012 10:26:00 am using timezone (GMT) Casablanca, Monrovia, Reykjavik.I didn't initially look at the documentation for that method because simply saw the parameter in the intellisense and used it. With the unexpected result, I went to the documentation to see it names the third parameter "tzOffsetToRemove". Well there's my problem. I takes the time I want to use and removes the timezone offset. Now we have another problem. It actually added the timezone offset instead of removing it. Remember the timezone offset is minus 6 hours. It actually subtracted the (incorrect) 5 hours. So instead of having a GMT time of 9:26 PM I have ... oh who knows what I have.
To show how insane this is, let's re-apply the timezone offset. Here's the code:
testDateTime = DateTimeUtil::applyTimeZoneOffset(testDateTime,What do you think this will give me? Here it is:
Timezone::GMTMINUS0600CENTRALTIME);
testDateTime is 11/2/2012 05:26:00 am using timezone (GMT) Casablanca, Monrovia, Reykjavik.
Of course, it removed the (incorrect) 5 hours again.
The final straw is this. If I create a GMT utcDateTime and then "remove" the timezone offset it actually sets the timezone to the one "removed" while actually removing the offset. Here is the code:
testDateTime = str2datetime('11/2/2012 15:26:00',213);You will recognize the first line of code from my first attempt. This takes a string and converts it to a GMT utcDateTime. Then, I call DateTimeUtil::removeTimeZoneOffset telling it to remove the CST timezone offset, hoping to end up with a GMT date/time. Here is what I get:
testDateTime = DateTimeUtil::removeTimeZoneOffset(testDateTime,
DateTimeUtil::getUserPreferredTimeZone());
testDateTime is 11/2/2012 08:26:00 pm using timezone (GMT-06:00) Central Time (US & Canada).I finally have a utcDateTime for CST but it contains the GMT time. Go figure.
So, the system attempts to provide time localization but instead manages to simply screw everything up. I don't know why they didn't just simply use the functionality built into the Windows OS since the client and server only run on Windows. I suspect the one hour difference is related to DST but I have not yet figured out how to take that into consideration.
Besides simply having buggy code, I think Microsoft has outdone themselves this time in complicating obfuscating their API. I would suggest they start by thinking how someone would want to use these utilities. Maybe someone would actually like to simply create a data and time for a specific timezone and have the value actually carry the timezone they specify.
Tory
Update:
I had to quit yesterday before I could test my last idea. I tested it this morning and found that by reapplying the removed timezone offset I finally ended up with the November 2nd 3:26 PM CST I was looking for. Here is the code and the result for the final (work around) solution:
testDateTime = str2datetime('11/2/2012 15:26:00',213);Result:
testDateTime = DateTimeUtil::removeTimeZoneOffset(testDateTime,
DateTimeUtil::getUserPreferredTimeZone());
testDateTime = DateTimeUtil::applyTimeZoneOffset(testDateTime,
DateTimeUtil::getUserPreferredTimeZone());
testDateTime is 11/2/2012 03:26:00 pm using timezone (GMT-06:00) Central Time (US & Canada).
