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
353347posts

How to filter by Timestamp in CearSCADA ODBC?

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
Operator112
Ensign
Ensign
0 Likes
2
954

How to filter by Timestamp in CearSCADA ODBC?

SELECT ID,VALUEAsReal, RecordTime, RecordId
FROM  CDBHISTORIC
WHERE RecordTime > {ts '2019-10-17 13:00:00'} AND ID = 40776 OR ID = 26114

I am trying to filter the results from CDBHISTORIC like so, I have also tried BETWEEN the chosen date and current timestamp but everything is giving me the same error. It returns all values from those ID's normally with out timestamp filtering.

Error in SQL statement: ODBC-call returned [-1] : [HY000][0][Control Microsystems][ClearSCADA database driver]Historic query not constrained by object, or could not be optimised efficiently

 What am I doing wrong?


Accepted Solutions
Operator112
Ensign
Ensign
0 Likes
1
951

Re: How to filter by Timestamp in CearSCADA ODBC?

Never god **bleep** mind, I literally found the solution two seconds after I posted this. Here is the correct SQL

 

SELECT ID,VALUEAsReal, RecordTime, RecordId
FROM  CDBHISTORIC
WHERE RecordTime > {ts '2019-10-17 13:00:00'} AND (ID = 40776 OR ID = 26114)

 

 You have to put the parenthesis () for the other filters: (ID = 40776 OR ID = 26114

*sigh* why is my life like this.

 

Some documentation about this can be found here: http://34.236.135.167/Help/Default.htm#SQLGuide/BuildtheSubClauseList.htm%3FTocPath%3DCore%2520Refer... 

See Answer In Context

2 Replies 2
Operator112
Ensign
Ensign
0 Likes
1
952

Re: How to filter by Timestamp in CearSCADA ODBC?

Never god **bleep** mind, I literally found the solution two seconds after I posted this. Here is the correct SQL

 

SELECT ID,VALUEAsReal, RecordTime, RecordId
FROM  CDBHISTORIC
WHERE RecordTime > {ts '2019-10-17 13:00:00'} AND (ID = 40776 OR ID = 26114)

 

 You have to put the parenthesis () for the other filters: (ID = 40776 OR ID = 26114

*sigh* why is my life like this.

 

Some documentation about this can be found here: http://34.236.135.167/Help/Default.htm#SQLGuide/BuildtheSubClauseList.htm%3FTocPath%3DCore%2520Refer... 

AdamWoodland
Commander Commander
Commander
0 Likes
0
921

Re: How to filter by Timestamp in CearSCADA ODBC?

AND takes precedence over OR, so your original query was really

 

Give me all historic where:

 

* RecordTime > {ts '2019-10-17 13:00:00'} AND ID = 40776

* OR give me all data for ID = 26114

 

When you added the brackets you made the logical part change