Querying historical data in SCADA systems - Step-by-Step Execution

Process Flow - Querying historical data

Start Query

↓

Specify Time Range

↓

Select Data Points

↓

Execute Query

↓

Retrieve Data

↓

Display or Analyze Data

↓

End

The flow shows how a historical data query starts by specifying time and data points, then executes, retrieves, and displays results.

Execution Sample

SCADA systems

SELECT timestamp, temperature FROM sensor_data
WHERE timestamp BETWEEN '2024-04-01 00:00:00' AND '2024-04-01 01:00:00'
ORDER BY timestamp;

This query retrieves temperature readings from sensor_data for one hour on April 1, 2024, ordered by time.

Process Table

Step	Action	Evaluation	Result
1	Start Query	Begin query process	Ready to specify parameters
2	Specify Time Range	Time range set to 2024-04-01 00:00 to 2024-04-01 01:00	Time filter applied
3	Select Data Points	Selecting 'timestamp' and 'temperature' columns	Columns selected
4	Execute Query	Run SQL query on database	Query sent to database engine
5	Retrieve Data	Database returns matching rows	Data rows received
6	Display or Analyze Data	Show data in table or graph	Data ready for user
7	End	Query complete	Process finished

💡 Query ends after data retrieval and display steps complete successfully

Status Tracker

Variable	Start	After Step 2	After Step 3	After Step 5	Final
time_range	None	'2024-04-01 00:00' to '2024-04-01 01:00'	'2024-04-01 00:00' to '2024-04-01 01:00'	'2024-04-01 00:00' to '2024-04-01 01:00'	'2024-04-01 00:00' to '2024-04-01 01:00'
selected_columns	None	None	['timestamp', 'temperature']	['timestamp', 'temperature']	['timestamp', 'temperature']
query_result	None	None	None	Data rows with timestamps and temperatures	Data rows with timestamps and temperatures

Key Moments - 2 Insights

Why do we need to specify a time range before executing the query?

Specifying the time range filters the data to only the relevant period, reducing the amount of data retrieved and speeding up the query, as shown in step 2 of the execution table.

What happens if we don't select specific data points?

If no columns are selected, the query might return all columns, which can be slow and overwhelming. Step 3 shows selecting only needed columns improves efficiency.

Visual Quiz - 3 Questions

Look at the execution table, what is the result after step 4 (Execute Query)?

A Data rows received

B Ready to specify parameters

C Query sent to database engine

D Process finished

💡 Hint

Check the 'Result' column for step 4 in the execution table.

At which step does the system retrieve the actual data rows?

A Step 3

B Step 5

C Step 2

D Step 6

💡 Hint

Look for the step labeled 'Retrieve Data' in the execution table.

If the time range was not specified, how would the variable 'time_range' appear after step 2 in variable_tracker?

A None

B ['timestamp', 'temperature']

C '2024-04-01 00:00' to '2024-04-01 01:00'

D Data rows with timestamps and temperatures

💡 Hint

Refer to the 'time_range' row in variable_tracker after step 2.

Concept Snapshot

Querying historical data involves:
- Specifying a time range to filter data
- Selecting relevant data points (columns)
- Executing the query on the database
- Retrieving and displaying the results
Always filter by time to improve speed and relevance.

Full Transcript

This visual execution trace shows how querying historical data in SCADA systems works step-by-step. First, the query process starts. Then, a time range is specified to limit data to a certain period. Next, specific data points like timestamp and temperature are selected. The query is executed on the database, which returns matching data rows. Finally, the data is displayed or analyzed, and the query process ends. Variables like time_range and selected_columns change as the query progresses. Key moments include understanding why time filtering is important and why selecting specific columns matters. The quiz questions help reinforce these steps by referencing the execution table and variable states.