Day light saving hours in queries

[Kan]

Hello everyone,

When I executed the same query on a Historic View from an external .NET application and then within ClearSCADA, I got different results. I realized that the difference was caused by the day-light saving hours. When the query was executed within ClearSCADA the day-light saving hours were recognized but not if it is called by the .NET application.

For example, my query contains the following condition

WHERE ( "Id" = 8668 ) AND ( "RecordTime" BETWEEN { OPC 'D-169D' } AND { OPC 'D-159D' } ) AND ( "Hour" = 0 )

and the ClearSCADA returns a list of values on Hour 1 rather than 0 because of the day-light saving.

But the results from query.ExecuteSync() using the same query text in the .NET application returns a list of values on Hour = 0.

I have been struggling with this for quite a while and I couldn't find anything wrong with my code. Right now, I think it is very likely the ClearSCADA C# API is buggy in executing queries.

Does anyone know how to fix or get around this problem?

Thanks a lot.

[BevanWeiss]

In the .NET Environment, you want to use DateTimeOffset types as much as possible whenever dealing with things related to times.

I can't seem to find the right thread to tug at in the .NET API help to get the Query details, you'll want to see what type it is returning for the data in the DateTime column, and what parameters it will accept.

You will also want to see what the Historic View is set to on the server.

Depending on when you want your aggregation to occur over, it's likely that this needs to be set to Local Time (with DST), of course because the aggregation occurs on the server, then there can be only one timezone applied (server time).

It's possible ViewX is doing tricky things, and getting the Historic View definition, and then performing this using custom historic aggregates.  So it's a little tricky to compare the .NET API and ViewX behaviour as comparing apples to apples... ViewX may not be doing what you think it's doing here.

I think it's very unlikely that the .NET API (it's not just C#.. it's a whole managed CLR API) is buggy.

I just think your assumption of what it 'should' do here might not be accurate.

PS: ViewX will definitely do "it's best" to convert times received into local ViewX timezone... even if that's not quite the right thing to do.  For example, if you said you want results with 'Hour=0' and it's giving you results with Hour=1, then I'd argue that ViewX is 'in the wrong' here... but again, it comes down to interpretation.  The TimeZone that the Query is being processed in (Server timezone, filtered by Historic View settings), and the ViewX TimeZone may not be the same.  If they aren't then you need to be careful of the transforms in both directions, as well as the processing performed at each end.