# Power table substract timevalue

**URL:** <https://forum.inductiveautomation.com/t/power-table-substract-timevalue/18651>\
**Category:** Ignition\
**Created:** [June 27, 2018, 8:12pm UTC](https://forum.inductiveautomation.com/t/power-table-substract-timevalue/18651 "2018-06-27T20:12:24Z")\
**Posts on this page:** 5\
**Page:** 1

<div class="post-metadata">

**Author:** ![tom.wouters](https://avatars.discourse-cdn.com/v4/letter/t/d2c977/32.png) [@tom.wouters](https://forum.inductiveautomation.com/u/tom.wouters)\
**Post date:** [June 27, 2018, 8:12pm UTC](https://forum.inductiveautomation.com/t/power-table-substract-timevalue/18651/1 "2018-06-27T20:12:24Z")

</div>

Hello,

I have a client where they want to log certain sequences’ start and stop times.

Some of you have helped me with this already (thanks again).

Now everything is working perfectly except my client wants to add a final touch. They want to make a column “Duration” where the time should be filled in how long the sequence has taken place.

Now my Column “Start time” and “Stop time” are both defined as varchar(max) inside the SQL DB. The value I write inside the query is “current\_timestamp”.

query = “INSERT INTO Logging (Nummer, Object\_Name, Object\_Type, Streng, Start\_Time) VALUES (?,?,?,?,current\_timestamp)”

Since both are actually strings that come up as for example “Jun 27 2018 2:24PM” I have no idea how I’m going to be able to substract the stop and start time to get the duration amount. If any1 has any ideas I would highly appreciate it.

Here is an example of my table.

 ![](https://us1.discourse-cdn.com/inductiveautomation/original/2X/d/d996f463eca233c8dd11f179792dd8ce0e7d0315.png)

---

<div class="post-metadata">

**Author:** ![Stuart](https://avatars.discourse-cdn.com/v4/letter/s/cab0a1/32.png) [@Stuart](https://forum.inductiveautomation.com/u/Stuart)\
**Post date:** [June 27, 2018, 8:25pm UTC](https://forum.inductiveautomation.com/t/power-table-substract-timevalue/18651/2 "2018-06-27T20:25:52Z")

</div>

First, did you make a few typos in the insert statements? Spelling “Number” incorrectly, and forgetting “Stop\_Time” in the column names. Corrected:

INSERT INTO Logging ( **Number** , Object\_Name, Object\_Type, Streng, Start\_Time, **Stop\_Time** ) VALUES (?,?,?,?,current\_timestamp)"

With `MySQL` there is a [String to Date](https://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_str-to-date) function that might be helpful.

I’ll see if I can come up with anything.

How are you triggering this insert? Is this done on a button press/etc through scripting? You may have to do a little work in scripting to make the calculations work.

---

<div class="post-metadata">

**Author:** ![phillip.bow](https://avatars.discourse-cdn.com/v4/letter/p/ce73a5/32.png) [@phillip.bow](https://forum.inductiveautomation.com/u/phillip.bow)\
**Post date:** [June 27, 2018, 8:28pm UTC](https://forum.inductiveautomation.com/t/power-table-substract-timevalue/18651/3 "2018-06-27T20:28:52Z")

</div>

What is your aversion to storing those date/time values in a proper date/time column type? You’d get this feature pretty much for free if your DB model matched what you’re actually trying to store.

---

<div class="post-metadata">

**Author:** ![pturmel](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pturmel/32/3295_2.png) [@pturmel](https://forum.inductiveautomation.com/u/pturmel)\
**Post date:** [June 27, 2018, 8:30pm UTC](https://forum.inductiveautomation.com/t/power-table-substract-timevalue/18651/4 "2018-06-27T20:30:42Z")

</div>

> [@phillip.bow](#):
>
> date/time values in a proper date/time column type?

Concur. Fix the DB table. Show us the error that caused you to abandon the proper column type.

---

<div class="post-metadata">

**Author:** ![Stuart](https://avatars.discourse-cdn.com/v4/letter/s/cab0a1/32.png) [@Stuart](https://forum.inductiveautomation.com/u/Stuart)\
**Post date:** [June 27, 2018, 8:52pm UTC](https://forum.inductiveautomation.com/t/power-table-substract-timevalue/18651/5 "2018-06-27T20:52:14Z")

</div>

I too vote for correct column type - why no datetime?

Anywho…  
Here is a way to display the difference between the two dates (if they are strings). In my test table (named “logging”) I have two colums; `Start_Time`, and `Stop_Time`. Both are VARCHAR and have data that looks like this: `2018-01-01 16:24:00`. the `str_to_date` function needs to be told what sort of format the string is in - you can refer to the [docs](https://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_date-format) to see what the formatting letters mean.

```auto
SELECT
	Start_Time_F,
    Stop_Time_F,
    TIMEDIFF(Stop_Time_F, Start_Time_F) AS Duration
FROM
    (
  SELECT 
	str_to_date(Start_Time,'%Y-%m-%d %k:%i:%s') AS 'Start_Time_F',
    str_to_date(Stop_Time,'%Y-%m-%d %k:%i:%s') AS 'Stop_Time_F'
    FROM logging
) AS outerTable

```

This returns the following.

```auto
   Start_Time_F | Stop_Time_F | Duration
--------------------------------------------------------
2018-01-01 14:24:00 |	2018-01-01 16:24:00 |	02:00:00
2018-03-01 14:24:00 |	2018-03-01 20:24:00 |	06:00:00

```

**THIS DOES NOT ACTUALLY STORE ANYTHING!** Just displays it.