Interesting, when I was writing post about time in Documentum, I have noticed that storing dates in UTC timezone would cause difficulties for reporting software, but I couldn’t imagine that everything is so really bad. The problem is that among all supported databases only Oracle database works correctly with timezones, i.e. I’m able to compensate any timezone offset in SQL selects by writing something like:
all other databases do not support timezone regions out of the box and work only with specific “(-|+)HH:MM” offsets, for example, in case of MSSQL in order to work correctly with UTC time (i.e. properly handle day light saving) you will need to install T-SQL Toolbox, which is slow by design. I do believe because of this Documentum’s DATETOSTRING_LOCAL function has so weird behaviour:
i.e. Documentum translates DATETOSTRING(r_creation_date, ‘yyyy-mm-dd’) directly to SQL’s TO_CHAR(dm_sysobject.r_creation_date, ‘yyyy-mm-dd’), but it unable to do the same for DATETOSTRING_LOCAL(r_creation_date, ‘yyyy-mm-dd’), in order to do that it converts date to string preserving information about time (i.e. TO_CHAR(dm_sysobject.r_creation_date, ‘mm/dd/yyyy hh24:mi:ss’)) and then converts resulting string to requested format and server’s timezone. Due to this implementation you are unable (actually you can, but results are unreliable) to use DATETOSTRING_LOCAL function in WHERE clause and subqueries.
Now, I do think that for the most installations storing dates in UTC timezone is an evil, and enabling this feature by default was a big EMC’s mistake.