Saturday, March 8, 2008

ITM 6.1 Data Warehouse UTC/GMT offset

The TMZDIFF in the ITM 6.1 Historical Database may appear to be incorrect. Usually when we see a time zone difference we see it represented as the difference from UTC (Coordinated Universal Time) to our local time zone. In the Eastern time zone (EST) we normally would expect to see a time zone difference as UTC/GMT -5 hours or -18000 seconds.

In the ITM 6.1 Historical database the WRITETIME and TIMESTAMP attributes are written as local timestamps. Therefore when you are looking at the TMZDIFF attribute in the database it appears to represents the difference between the local time and the UTC/GMT (e.g., +18000 seconds for EST).

For example, the last entry in my ITM 6.1 Historical Database 'NT_Processor'table has WRITETIME = "1060330083000000" and TMZDIFF = 18000. This tells me that the local write time for this record was 08:30 and the UTC/GMT at that time was +18000 seconds (i.e., 13:30).