Create column with time difference between two other columns

Hello, I wanted to know if anyone can help me to create a column with the time difference between two columns with datetime, I need to calculate the elapsed time between the times that I save in the database, a ticket has various actions that are recorded in the database and I need to know how much time has passed between steps. I have created a table with the id number which is the same in the various steps, a column with start, one with pending, one with closed which have the datetime structure, I have created another column with total time where I would like to calculate the time difference but I still can't save and have the data stored in the database and the calculated time also seems wrong to me.

SELECT
  ticket_id,
  start,
  pending,
  MOD(pending, 60) AS total
FROM logs
WHERE start IS NOT NULL
OR pending IS NOT NULL

SELECT `ticket_id`, `start`, `pending `, TIMEDIFF(MAX(`start `), MIN(`pending `)) AS total
FROM `inizios`
WHERE start IS NOT NULL
OR pending  IS NOT NULL
GROUP BY `ticket_id`

LaryAI

3 years ago

Best Answer

Level 58

To create a column with the time difference between two datetime columns in MySQL, you can use the TIMEDIFF function. Here's an example query that calculates the time difference between the "start" and "pending" columns and stores the result in a new column called "total_time":

ALTER TABLE logs ADD COLUMN total_time TIME;

UPDATE logs SET total_time = TIMEDIFF(pending, start);

This will add a new column called "total_time" to the "logs" table and populate it with the time difference between the "start" and "pending" columns for each row.

Note that the TIMEDIFF function returns a time value, which is a duration in the format "HH:MM:SS". If you want to store the duration in a different format, you can use the TIME_FORMAT function to convert it to a string. For example:

ALTER TABLE logs ADD COLUMN total_time VARCHAR(10);

UPDATE logs SET total_time = TIME_FORMAT(TIMEDIFF(pending, start), '%H:%i:%s');

This will store the duration as a string in the format "HH:MM:SS".

marcoplus

3 years ago

Level 6

@LaryAI Thanks for the reply but you return me a 0 value with this query

ALTER TABLE logs ADD COLUMN total_time TIME;

UPDATE logs SET total_time = TIMEDIFF(peding, start);

Query SQL:
UPDATE logs SET total_time = TIMEDIFF(peding, start)
Rows involved: 0

perhaps because it draws the time in the same line and not in the same ticket_id number, this is the table with the various hourly steps, each line enter the start, suspended, closed time

Snapey

3 years ago

Level 122

Note that with Lary answer, the start and end must be in the same record

marcoplus

3 years ago

Level 6

@Snapey I really need the calculation to be performed on the same id because I created a column with the same id which contains the times elapsed between the various steps that are there

marcoplus

3 years ago

Level 6

looking for a solution I found this code that seems to work and returns the value I need but I don't know how to write and save the result in the column to be able to use the result in a view, help to complete this? Now i see the result correctly, how do i save the calculated result in a column? or maybe convert to laravel query builder Eloquent ORM maybe i could call this directly from controller in laravel?

WITH inizios AS (
    SELECT ticket_id, stato, time_stamp, (LEAD(stato) OVER (ORDER BY time_stamp) ) AS to_stato,
    SEC_TO_TIME(TIMEDIFF(LEAD(time_stamp) OVER (ORDER BY time_stamp), time_stamp)) AS time_diff
    FROM inizios
    )
    SELECT ticket_id, time_diff
    FROM inizios
    WHERE stato='Assigned' AND to_stato IN ('ActivityStarted', NULL)

Please or to participate in this conversation.