AF Client Time Stamp not reference correctly

Discussion created by tgreen on Jun 12, 2014
Latest reply on Jul 7, 2014 by tgreen

Not sure if this is the best place for this, as I don't believe it's really an SDK question




We lost the AF SQL DB the other day and of course the backups (why not hey?).  Since it was a test environment, it's not a huge deal

  • I repaired SQL server and got rid of the dead references (the drive that the AF DB was on was lost).  So the fresh DB is now available
  • I did a re-install of PI-AF-Server_2014_.exe
  • Did a reinstall of PI-AF-Client_2014_.exe (just in case)
  • Neither were uninstalled
  • I of course was prompted that the database was not there, gave it a name and the AF structure was built in SQL
  • I did have saved exports of the AF templates and elements via PI System Explorer.  Loaded the most recent up



so, now here's the question:


When referencing external queries (same SQL server, different database this backup was able to be recovered, but is old data), my tables seem functional and connectable as the data is returned without an issue


When I go to the element, the Time Stamp is the current system time, but I'm returning 'Table provided time series data' and this was functional before




SQL  column 'Timestamp' is set to the default SQL DateTime


SQL Raw timestamp value: '2014-03-28 14:04:48.000'


AF Resultant table = '3/28/2014 2:04:48 PM'


This is in UTC, so in the Table config:

  • "Define Table" tab, the Timestamp column is set to UTC
  • "General" Tab , Time Zone is set to UTC



Note: if I change this from 'Table provided time series data' to 'Select first row matching criteria' I return the expected value (original SQL query is ... order by Timestamp desc)


If I use the  'Table provided time series data' so that the 'Time Stamp' is reflecting the data correctly using a sample Table Lookup like below:


SELECT Value1 FROM Table1 WHERE [KeyID] = '%Element%' and Timestamp <= #%Time%# ORDER BY Timestamp desc;TC=Timestamp




My value returns this:


Timestamp the selected Time Column used for time series data is not valid. It must be a DateTime data type and must not have a column time zone defined.




Again, this was working prior to the loss of the database




Any ideas!?


Thanks in advance!!