How to calculate difference of dates that are a previous row with MYSQL?

Question

How to calculate difference of dates that are a previous row with MYSQL?

Navigation

#1 by (3 votes)
#2 by (2 votes)
#3 by (1 votes)

2

I have a table where I record the time when a given request had its status_id changed. This table I call historico_status_solicitacoes .

In this table, I have the following fields: id , status_id , solicitacao_id , usuario_id , created_at .

For each request that has changed status, a record is entered.

So:

+----+-----------+----------------+------------+---------------------+
| id | status_id | solicitacao_id | usuario_id | created_at          |
+----+-----------+----------------+------------+---------------------+
|  1 |         1 |              1 |          1 | 2018-10-29 17:28:46 |
|  2 |         2 |              1 |          1 | 2018-10-29 17:38:50 |
|  3 |         3 |              1 |          1 | 2018-10-29 17:48:16 |
|  4 |         4 |              1 |          1 | 2018-10-29 18:58:46 |
|  5 |         5 |              1 |          1 | 2018-10-29 19:31:46 |
|  6 |         6 |              1 |          1 | 2018-10-30 10:20:00 |
+----+-----------+----------------+------------+---------------------+

What do I need to do? I need to make a query, where I can compute the difference in minutes between one row and another, considering the smallest date for the largest date (which in this case can be id also, since it is created sequentially, and the largest value is always the most recent).

I've done something similar to calculate the current balance, using the previous line, but I have no idea how to work with time difference in MYSQL.

Can anyone help?

mysql data

asked by anonymous 01.11.2018 / 21:09

3 answers

How to adjust Background to fit inside container without cutting anything? counter function of reshape2 :: melt (tear)

score 3 · Answer 1

In the query below it relates to the smallest ID that is greater than the current

SELECT 
    A.id,
    A.created_at, 
    TIMESTAMPDIFF(SECOND,A.created_at,B.created_at) AS timedifference 
FROM historico_status_solicitacoes A 
LEFT JOIN historico_status_solicitacoes B ON B.id = (
        SELECT MIN(B2.ID) FROM historico_status_solicitacoes B2 WHERE B2.ID>A.ID
    )

ORDER BY A.id ASC

score 2 · Answer 2

You can make a SUB-SELECT by taking all the records with dates smaller than the current one, sort by date in descending order and limit by 1, if all records really have the sequential date, you will always take the previous one:

select a.id, a.status_id, a.solicitacao_id, a.usuario_id, a.created_at, IFNULL((
    select TIMESTAMPDIFF(MINUTE, DATE_FORMAT(a1.created_at, '%Y-%m-%d %H-%i-00'), DATE_FORMAT(a.created_at, '%Y-%m-%d %H-%i-00'))
    from historico_status_solicitacoes a1
    where a1.id <> a.id AND a1.created_at <= a.created_at
    ORDER BY a1.created_at DESC
    LIMIT 1
), 0) diff
from historico_status_solicitacoes a;

I'm using the DATE_FORMAT function to ignore the seconds and always calculate the full minute.

See working in SQL Fiddle .

score 1 · Answer 3

Wallace the query can be done as follows:

SELECT A.id, A.created_at, TIMESTAMPDIFF(SECOND,A.created_at,B.created_at) AS timedifference 
FROM historico_status_solicitacoes A INNER JOIN historico_status_solicitacoes B ON B.id = (A.id + 1) 
ORDER BY A.id ASC

Just a note, in this case the result will be in 'SECONDS' , to leave in a greater granularity (Minutes or hours), just change the following excerpt in the SQL syntax:

... TIMESTAMPDIFF (SECOND, ...