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

Asked

Viewed 881 times

2

I have a table where I record the time when a particular 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 the status changed, a record is entered.

Thus:

+----+-----------+----------------+------------+---------------------+
| 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 do a query, where I can compute the difference in minutes between a line and another, considering the shorter date for the longer date (which in this case may be the id also, since it is created sequentially, and the highest value is always the latest).

I did something similar to calculate the current balance using the previous line, but I have no idea how to work with date difference in MYSQL.

Someone can help out?

mysql date

  • Geez, there’s not 10 seconds I posted the question and there’s already a negative? What’s going on? Could you explain why?

  • 2

    I wasn’t the one who said no, but why do it in the bank? You can’t go straight to the front?

  • @Guilhermecostamilam if there are too many returns, I might want to resort to a pagination. And in a pagination, you wouldn’t have the previous value if you were on the second page. I even thought of asking the question in the sense of taking it to the side of Laravel, but in this case, I prefer in the same database.

  • 1

    But you have to think about the performance issue as well, this can be very important for the bank, something that would be simple on the front end. ps.: but I was not the one who negative xD

  • @aa_sp honestly, depending on the size of the pagination, it would make up more to do the calculation in the bank, wouldn’t it? Why a status could be repeated in the history. Of course it is a rare case, but let’s assume that there is a case of me having 1000 records. It’s good to think on this side, because the goal of the site is to have a more canonical response that can help other users.

  • 1

    @Wallacemaxters need to evaluate, depending on the times a simple comparison variable would solve. But Voce, who knows the system, needs to evaluate and test the options, thinking not only how the base is now, but in the future.

  • 1

    @Wallacemaxters, wouldn’t it be interesting to create a column diferenca and at the time of inserting calculate and inform the difference for the last record?

  • @Robertodecampos is against database normalization, isn’t it? Calculating the data is more likely to have the right value than a column that can be erroneously changed.

Show 3 more comments

3 answers

3


In the query below it relates to the smallest ID that is larger 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

3

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

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 function DATE_FORMAT to ignore the seconds and always calculate the full minute.

See working on SQL Fiddle.

1

Wallace the query can be made 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 one observation, in which case the result will be 'SECONDS', to leave in a larger granularity ( Minutes or hours), just change the following snippet in the SQL syntax:

...TIMESTAMPDIFF(SECOND,...

  • 1

    Good idea. The problem is that sometimes the id can’t always be +1 the subsequent.

  • understood.. is that I based on this part: (which in this case can be id too, since it is created sequentially).

  • I think I expressed myself badly. Actually, the id most recent will always have the value greater than the previous.

Browser other questions tagged mysql date

You are not signed in. Login or sign up in order to post.