Welcome to the new Schneider Electric Community

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

Close
Important Announcement: WELCOME to the new Schneider Electric Community! Community is now no longer part of Exchange, and is now rebranded under se.com. If you have any bookmarks and links saved, we request you to update them to ensure that you continue accessing our community from this new location. For any issues that you might encounter as part of this change, please reach out to SchneiderCommunity.Support@se.com, and the team will help to get your issues resolved.
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
82537members
350142posts

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
915

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
912

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
913

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
882

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