Welcome to the new Schneider Electric Community

It's your place to connect with experts and peers, get continuous support, and share knowledge.

  • Explore the new navigation for even easier access to your community.
  • Bookmark and use our new, easy-to-remember address (community.se.com).
  • Get ready for more content and an improved experience.

Contact SchneiderCommunity.Support@se.com if you have any questions.

Close
Invite a Co-worker
Send a co-worker an invite to the Exchange portal.Just enter their email address and we’ll connect them to register. After joining, they will belong to the same company.
Send Invite Cancel
84248members
353348posts

[Imported] Extracting date and time of first history stored for a point

EcoStruxure Geo SCADA Expert Forum

Find out how SCADA systems and networks, like EcoStruxure Geo SCADA Expert, help industrial organizations maintaining efficiency, processing data for smarter decision making with IoT, RTU and PLC devices.

Solved
sbeadle
Janeway Janeway
Janeway
0 Likes
1
392

[Imported] Extracting date and time of first history stored for a point

>>Message imported from previous forum - Category:Scripts and Tips<<
User: <None>, originally posted: 2019-06-20 04:09:03 Id:455
I would like to Search through the point history of a site and extract the very first date and time that history starting collecting for the site. Does anyone have an SQL query that can do this?


Accepted Solutions
sbeadle
Janeway Janeway
Janeway
0 Likes
0
391

Re: [Imported] Extracting date and time of first history stored for a point

>>Responses imported from previous forum


Reply From User: adamwoodland, posted: 2019-06-20 23:00:16
How efficient do you need this to be?

If inefficient then you could just do "SELECT TOP(1) * FROM CDBHistoric WHERE Id = [your object id] ORDER BY RecordTime ASC", it might take a long time depending on how much data and how dense the data is. CDBHistoric is disk resident so performance also depends on disk speed (and is relatively slow).

If you need it to be efficient (i.e. multiple points or needing to run often), then you should first query CDBHistoricFile (which is memory resident), look for the oldest granule for that point id with at least one record, and then just query that specific granule for its oldest record, i.e.:

SELECT TOP( 1 ) FILEID, FILETYPE, STARTTIME FROM CDBHISTORICFILE WHERE OBJECTID = 3793274 AND RECORDCOUNT = 1 AND FILETYPE = 0 ORDER BY "StartTime" ASC

Then for the next query:

SELECT TOP( 1 ) RECORDTIME, VALUE FROM CDBHISTORIC WHERE ID = 3793274 AND FILEID = '0039E17A0000554AR' ORDER BY "RecordTime" ASC

(Where 0039E17A0000554AR is the FileId from the first query)

 

See Answer In Context

1 Reply 1
sbeadle
Janeway Janeway
Janeway
0 Likes
0
392

Re: [Imported] Extracting date and time of first history stored for a point

>>Responses imported from previous forum


Reply From User: adamwoodland, posted: 2019-06-20 23:00:16
How efficient do you need this to be?

If inefficient then you could just do "SELECT TOP(1) * FROM CDBHistoric WHERE Id = [your object id] ORDER BY RecordTime ASC", it might take a long time depending on how much data and how dense the data is. CDBHistoric is disk resident so performance also depends on disk speed (and is relatively slow).

If you need it to be efficient (i.e. multiple points or needing to run often), then you should first query CDBHistoricFile (which is memory resident), look for the oldest granule for that point id with at least one record, and then just query that specific granule for its oldest record, i.e.:

SELECT TOP( 1 ) FILEID, FILETYPE, STARTTIME FROM CDBHISTORICFILE WHERE OBJECTID = 3793274 AND RECORDCOUNT = 1 AND FILETYPE = 0 ORDER BY "StartTime" ASC

Then for the next query:

SELECT TOP( 1 ) RECORDTIME, VALUE FROM CDBHISTORIC WHERE ID = 3793274 AND FILEID = '0039E17A0000554AR' ORDER BY "RecordTime" ASC

(Where 0039E17A0000554AR is the FileId from the first query)