# SQL Bridge - Measure duration time of a batch

**URL:** <https://forum.inductiveautomation.com/t/sql-bridge-measure-duration-time-of-a-batch/102215>\
**Category:** Ignition\
**Tags:** perspective\
**Created:** [February 11, 2025, 10:13pm UTC](https://forum.inductiveautomation.com/t/sql-bridge-measure-duration-time-of-a-batch/102215 "2025-02-11T22:13:32Z")\
**Posts on this page:** 3\
**Page:** 1

<div class="post-metadata">

**Author:** ![James\_Toor](https://avatars.discourse-cdn.com/v4/letter/j/ce73a5/32.png) [@James\_Toor](https://forum.inductiveautomation.com/u/James_Toor)\
**Post date:** [February 11, 2025, 10:13pm UTC](https://forum.inductiveautomation.com/t/sql-bridge-measure-duration-time-of-a-batch/102215/1 "2025-02-11T22:13:32Z")

</div>

I have just started using the SQL Bridge for the first time and I'm having some trouble getting my head around setting it up to measure the duration time between start\_time and finish\_time in a database for a production batch record using the SQL Bridge module.

There are 3 batching units which I am trying to record times on the same table. A set of transaction groups have been setup for each unit (unit 1 shown below).

Currently I have the following transactions for each unit:  
**Unit 1 Batch Start:**  
_Basic OPC/Group Items:_  
Unit\_Select | OPC TAG | INT | Target: database-unit\_number  
start\_time | OPC TAG | [System]Gateway/CurrentDateTime | DATETIME | Target: database-start\_time  
Action: OPC to DB / Insert new row  
Trigger: Unit\_Select | is active = (unit number int value 1)

**Unit 1 Batch Finish**  
_Basic OPC/Group Items:_  
Unit1\_Discharge\_Valve | OPC TAG | bool | Target: none, read only  
finish\_volume | OPC TAG | float | Target: database-finish\_volume  
finish\_time | OPC TAG | [System]Gateway/CurrentDateTime | DATETIME | Target: database-finish\_time  
Action: OPC to DB / Update-custom: unit\_number = 1 AND finish\_time is null  
Trigger: Unit1\_Discharge\_Valve | is ! = 0

These two transactions are working ok.

To try and write the duration\_minutes, in the Batch Finish Group I also used the below SQL query to measure and write the duration time however as the table did not yet have the finish\_time when executed, it would not write the value until the batch after this one was completed many hours later.  
_ **Triggered Expression Items:** _  
duration\_time | int | Expression Type: SQL Query | Target: database-duration\_minutes

```sql
UPDATE batch_times
SET duration_minutes = TIMESTAMPDIFF(MINUTE, start_time, finish_time)
WHERE unit_number = 1 
AND finish_time IS NOT NULL
ORDER BY start_time DESC 
LIMIT 1;

```

I also tried using `TIMESTAMPDIFF(MINUTE, start_time, NOW())` instead but I was still returning null values.

So I tried setting up a third Transaction for each unit that would  
use the finish\_time changing from NULL to NOT NULL as a trigger, then calculate the duration and write the duration using the SQL Query from above.

However I am struggling to implement this and getting the trigger to work, I have the following expression sql query tags setup as below.

I'm not sure if I am close or I have overcomplicated the whole process from the start?

Any help would be much appreciated, thanks.

**finish\_time\_null - Run always - Trigger - Active on Value Change**

```sql
SELECT finish_time 
FROM batch_times
WHERE unit_number = 1
AND start_time IS NOT NULL
AND finish_time IS NULL 
AND duration_minutes IS NULL
ORDER BY start_time DESC 
LIMIT 1;

```

**start\_time - Run always**

```sql
SELECT start_time 
FROM batch_times 
WHERE unit_number = 1 
AND finish_time IS NULL 
AND duration_minutes IS NULL
ORDER BY start_time DESC 
LIMIT 1;

```

**finish\_time - Run always**

```sql
SELECT finish_time 
FROM batch_times
WHERE unit_number = 1 
AND finish_time IS NOT NULL 
AND duration_minutes IS NULL
ORDER BY start_time DESC 
LIMIT 1;

```

**duration\_minutes - Target - Database-duration\_minutes**

```sql
UPDATE batch_times 
SET duration_minutes = TIMESTAMPDIFF(MINUTE, start_time, finish_time)
WHERE unit_number = 1 
AND finish_time IS NOT NULL 
AND duration_minutes IS NULL
ORDER BY start_time DESC 
LIMIT 1;

```

**Database: MySQL**

```sql
Table: batch_times
Columns:
id | int AI PK
unit_number | int 
batch_date | date
product_name | varchar(50)
start_time | datetime
finish_time | datetime
duration_minutes | int
finish_volume | float
created_at | timestamp
t_stamp | datetime

```

---

<div class="post-metadata">

**Author:** ![JordanCClark](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/jordancclark/32/83362_2.png) [@JordanCClark](https://forum.inductiveautomation.com/u/JordanCClark)\
**Post date:** [February 12, 2025, 10:04pm UTC](https://forum.inductiveautomation.com/t/sql-bridge-measure-duration-time-of-a-batch/102215/3 "2025-02-12T22:04:55Z")

</div>

Don't bother with duration as a db column. calculate in your query when you select it.

---

<div class="post-metadata">

**Author:** ![James\_Toor](https://avatars.discourse-cdn.com/v4/letter/j/ce73a5/32.png) [@James\_Toor](https://forum.inductiveautomation.com/u/James_Toor)\
**Post date:** [February 22, 2025, 11:11pm UTC](https://forum.inductiveautomation.com/t/sql-bridge-measure-duration-time-of-a-batch/102215/4 "2025-02-22T23:11:09Z")

</div>

Thanks, that was alot easier!