VantagePoint and Daylight savings time (aka DST hell)

[radfahrer]

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.

 

[JaxGTO]

I have found that creating a SQL Stored Procedure is the best way to collect data for a report. It gives you much more flexibility when gathering data. You might want to try that. Then you can create temporary tables and fill them with your data in the order you want, then set them as the output data.

 

[I_Automation]

For logging like this I don't use local time.



Being old & stubborn I still call it Greenwich Mean Time, based on the zero hour line that runs through Greenwich, England - but it is now officially called UTC time. You can then display the local time next to the UTC time, just use EST or EDT (for Eastern) depending if standard or daylight time.


I also have seen at least one application that always logged EST year round, so it reported off 1 hour during DST. This did have a notice on the HMI log screen that it was not DST.