Power table substract timevalue

Ignition
1

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.

CIPloggingExample.png1175×121 3.71 KB

2

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 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.

3

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.

3 Likes
4

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

1 Like
5

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 to see what the formatting letters mean.

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.

   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.