AnsweredAssumed Answered

PI OLEDB Enterprise and DST: how to make this work?

Question asked by Roger Palmen on Feb 1, 2016
Latest reply on Mar 16, 2016 by Roger Palmen

Hi all,

 

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:

  1. OLEDB Enterprise reports all data in local timezone, so i need to know the local timezone offset:
    SET @DSToffset = DATENAME(TZOFFSET, SYSDATETIMEOFFSET())
  2. 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?

Outcomes