Using PI SQL Commander Lite, I'm using the ft_summarize table under data > tables and I'm fetching the max value for each day starting may 1st. I get the max value, but how do I get to the timestamp for that value?
Update: It seems if you change the TimeType parameter to 'Auto' instead of 'MostRecentTime' and include the Time column in your query everything is happy. I verified in OLEDB Ent and RTQP.
I took some time to look into this and found that the Summarize function table doesn't correctly display the timestamp of the Min/Max of the sampled summary. I have informed the development team of this oversight and have created a bug item to address. The behavior is consistent in the upcoming release of RTQP which is meant to replace OLEDB Enterprise entirely so the first place to look for a fix would be there.
There is a potential workaround using PI OLEDB Classic Provider in that the PIMAX table exposes a "timeofmax" column that provides precisely what you are looking for. The downside is that turns your query into something tag-based and not asset-based.
Can you please share your query?
I get a Time field, but its just the date, no time.
SELECT eh.Name Element, ea.Name Attribute, s.Value, s.Time
FROM [Default].[Asset].[ElementHierarchy] eh,
SELECT N'Maximum' SummaryType
INNER JOIN [Default].[Asset].[ElementAttribute] ea ON ea.ElementID = eh.ElementID,
WHERE eh.Path LIKE N'\Circuits\%'
AND s.ElementAttributeID = ea.ID
AND s.StartTime = N'5/01/2018'
AND s.EndTime = N't'
AND s.TimeStep = N'1d'
AND s.SummaryType = t.SummaryType
AND s.CalculationBasis = N'TimeWeighted'
AND s.TimeType = N'MostRecentTime'
AND ea.Name = 'MVA'
AND eh.Name = '1036'
OPTION (FORCE ORDER, EMBED ERRORS)
Retrieving data ...