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:
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)