We have a client with 24/7 operations with batch reports that include queries of process values from an FT Historian. They do the reports in SQL Reporting services using function calls to VantagePoint to pull the data from the Historian. The issue we're trying to solve is how to handle reports that cover the 2-3 AM period of Fall DST transition- ie where 2-3 AM happens twice (on the east coast, so it's once as EDT and then once as EST - spring isn't an issue since there is a just a gap from 2-3 not an overlap, so all the data is still visible)
We know the Historian is storing as UTC, so in the DB there is no overlap, but the question is how to have the client application (SQL/VantagePoint) get that data. Just curious if anyone else has dealt with an issue like this.
interweb searches turn up lots of SQL examples for trying to deal with DST, but they get very complex and we're trying to keep it simple. Since this only happens once a year and probably only impacts one or two batches a year at the most, a manual workaround is acceptable. Our initial thought is to try to create a special report that would run the queries passing the start/end time in UTC and they could run that for a couple hours for that overnight and attach it to the original report and they could see the missing hour of data in that attachment, but we're not sure how to get VantagePoint to return data using a time zone different than the current time zone of the server.
Any tips, tricks or ideas/examples from anyone who has encountered a similar situation are appreciated.
We know the Historian is storing as UTC, so in the DB there is no overlap, but the question is how to have the client application (SQL/VantagePoint) get that data. Just curious if anyone else has dealt with an issue like this.
interweb searches turn up lots of SQL examples for trying to deal with DST, but they get very complex and we're trying to keep it simple. Since this only happens once a year and probably only impacts one or two batches a year at the most, a manual workaround is acceptable. Our initial thought is to try to create a special report that would run the queries passing the start/end time in UTC and they could run that for a couple hours for that overnight and attach it to the original report and they could see the missing hour of data in that attachment, but we're not sure how to get VantagePoint to return data using a time zone different than the current time zone of the server.
Any tips, tricks or ideas/examples from anyone who has encountered a similar situation are appreciated.