Has anyone else had strange issues with utcDateTime and Timezone offsets?
We are working on an implementation and importing employee data. Some of the data is hire dates and other employment related dates. Many of these dates use the DateTime 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.
When I attempt to convert the date & time from the CSV file like this:
utcDateTime testDateTime;
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 I tried to apply a timezone offset to this time. My timezone is CST which is GMT - 6 hours. The timezone enum for this is Timezone::GMTMINUS0600CENTRALTIME.
testDateTime = DateTimeUtil::applyTimeZoneOffset(testDateTime,
DateTimeUtil::getUserPreferredTimeZone());
I can't figure out what how I got this:
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(
str2Date('11/2/2012', 213),
str2time('15:26:00'),
Timezone::GMTMINUS0600CENTRALTIME);
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:
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 a GMT time of 10:26 AM. I'm getting further from what I intended.
To show how insane this is, let's re-apply the timezone offset. Here's the code:
testDateTime = DateTimeUtil::applyTimeZoneOffset(testDateTime,
Timezone::GMTMINUS0600CENTRALTIME);
What do you think this will give me? Here it is:
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);
testDateTime = DateTimeUtil::removeTimeZoneOffset(testDateTime,
DateTimeUtil::getUserPreferredTimeZone());
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 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.
Has anyone else had similar issues? Can anyone shed some light on this? Maybe I'm really screwed up and simply don't understand something. But this sure has me confused.
Thanks,
Tory
