One of my favorite difficult topics: Timezones and DST. I was just writing a little procedure to pull EventFrames from PI OLEDB Enterprise into SQLserver (2008R2). Trying to make this work properly across timezones, i decided to use the datetimeoffset datatype in SQLserver, so that i could store the timezone information along with the timestamps. Looks quite simple:
- OLEDB Enterprise reports all data in local timezone, so i need to know the local timezone offset:
SET @DSToffset = DATENAME(TZOFFSET, SYSDATETIMEOFFSET())
- And when storing the local timestamps from the eventframe, apply the offset:
SELECT SWITCHOFFSET(StartTime, @DSToffset) FROM <<Linked Server>>.EventFrame.EventFrame
Simple as 1-2.
But then moving on to DST. When i create (e.g. in the europe, using DST, moving 1 hour forward on March 28) i create an eventframe starting March 28, 1PM and ending March 29, 1PM. I get an eventframe that lasts 23 hours (get start- and endtime into a DateTime attribute and subtract, cast back to DateTime). So that looks promising!
However, i cannot apply the current timezone offset (now it's winter) to the endtime in the summertime. So how should i know the timezone a timestamp is reported in when looking at it from OLEDB Enterprise?
Yes, it's always local, but does that mean that for EVERY timestamp, i need to lookup what the specific offset is? Here in NL, in wintertime 1 hour, in summertime 2 hours.
This enhancement request tells me it's implemented in version 1.4, but no sign of that in the documentation: https://techsupport.osisoft.com/Troubleshooting/Enhancements/120521
I would expect that at least i could specify UTC timestamps to be returned in the OLEDB Initialisation parameters, like the PI OLEDB Provider.
Anyone any thoughts / comments / workarounds?
Or did i overlook something?