Aveva/wonderware Historian Query returning wrong time format

Just loaded Open Source Graphna to explore and test for use (v7.5.5). Linking to wonderware/Aveva historian. which uses a SQL server, but some “custom” query language to access the realtime database behind the SQL server.

No issues connecting to the SQL and it can pull data to a table but not time series for the trends. Hoping to figure out how to build the SQL query to return a DateTime format that Graphna will accept. Date times seem to come across but not in UTC. I have not found a good combination that returns both UTC and “time” column name. The SQL just fail’s out when attempting both.

SET NOCOUNT ON
DECLARE @StartDate DateTime
DECLARE @EndDate DateTime
SET @StartDate = ‘20210503 02:41:11.000’
SET @EndDate = ‘20210503 02:46:11.000’
SET NOCOUNT OFF
SELECT * FROM (
SELECT DateTime = convert(nvarchar, DateTime, 127), Value, StartDateTime
FROM History
WHERE History.TagName IN (‘Test_Tag_Here’)
AND wwRetrievalMode = ‘Cyclic’
AND wwCycleCount = 100
AND wwQualityRule = ‘Extended’
AND wwVersion = ‘Latest’
AND DateTime >= @StartDate
AND DateTime <= @EndDate) temp WHERE temp.StartDateTime >= @StartDate
ORDER BY time ASC

I wish I was able to assist. I am curious on how you were able to link the historian to Grafana. I am new to queries and Grafana and i cannot seem to figure out how to push our Aveva data into Grafana.

This topic was automatically closed after 365 days. New replies are no longer allowed.