PolyMon

Monitor * Measure * Analyze * Alert

Time Zones

One thing I really wanted to have in the 2.x version was proper time zone handling.
In part this is due to having operator calendars where fully customizable calendars can be set up for each individual operator indicating their On/Off times (i.e. when can an operator be notified) which will also tie into escalation procedures associated with monitor alerts.

I initially thought this would be easy given SQL 2008's new datetimeoffset data type. However, things are never as easy as you think...

This article explains some of the issues and solutions quite well:
blogs.msdn.com/sqlprogrammability/archive/2008/03/18/using-time-zone-data-in-sql-server-2008.aspx

I have used this approach for PolyMon and the following tables and user defined functions are available:
  • TZMapping: Table that contains a list of UTC zone offsets (no DST)
  • TZZones: Table that contains the DST offsets for each time zone per date range (since DST rules change over time).
  • tx_UTCOffset: Function that calculates the UTC offset for a date time + zone (the offset can be used as follows: UTC + Offset --> Local)
  • tx_LocalToUTC: Converts a Local date time to UTC (given time zone)
  • tc_UTCToLocal: Converts UTC to Local time (given time zone)
You can download a SQL script that creates the tables and functions as well as populates the time zone data here.

0 comments:

Label Cloud

About Me

My photo
You can contact me via email here: Google Profile
Using the StudioPress WordPress Theme, Bloggerized by Girly Blogger for BTemplates.