How to Combine Two Rows and Calculate the Time Difference Between Two Timestamp Values in MySQL

Calculate the time difference between of two rows

To achieve what you are asking try the following (UPDATE after edit from OP):

SELECT A.requestid, A.starttime, (B.starttime - A.starttime) AS timedifference
FROM MyTable A INNER JOIN MyTable B ON B.requestid = (A.requestid + 1)
ORDER BY A.requestid ASC

IF requestid is not consecutive then you can use

SELECT A.requestid, A.starttime, (B.starttime - A.starttime) AS timedifference
FROM MyTable A CROSS JOIN MyTable B
WHERE B.requestid IN (SELECT MIN (C.requestid) FROM MyTable C WHERE C.requestid > A.requestid)
ORDER BY A.requestid ASC

calculate time difference in minutes between timestamp from the same column mysql

I'm not a fan of putting subqueries in the SELECT but it's a way of solving this problem for ancient versions of MySQL:

SELECT 
  transaction_pk,
  status_timestamp,
  (SELECT MAX(tsub.status_timestamp) FROM t tsub WHERE tsub.transaction_pk = tmain.transaction_pk and tsub.status_timestamp < tmain.status_timestamp) as last_timestamp
FROM
  t tmain

You can use it like you would any other value in e.g. a call to timestampdiff:

SELECT 
  transaction_pk,
  status_timestamp,
  COALESCE(TIMESTAMPDIFF(
    SECOND,
    (SELECT MAX(tsub.status_timestamp) FROM test tsub WHERE tsub.transaction_pk = tmain.transaction_pk and tsub.status_timestamp < tmain.status_timestamp),
    status_timestamp
  )/60.0, 0) as diff
FROM
  t tmain

The COALESCE converts the NULL that results from the first value per PK not having any "timestamp less than the current", into a 0.. otherwise, the idea of diffing on the seconds then dividing by 60 gives us a decimal representation of the number of fractional minutes since the previous time. If you need greater precision, use a smaller time interval and a greater divisor respectively

Mysql Calculate time difference between timestamps in same field?

You can self-join the table, something like this works in your case:

SELECT 
*
FROM
yourTable a
INNER JOIN yourTable b ON a.ID = b.ID + 1
WHERE TIMESTAMPDIFF(second, a.timestamp, b.timestamp) > 60

But this can get ugly when you have gaps in your ID column. And especially it can get ugly (in terms of performance (when you don't have good indexes on the table)) when you have *lots of data.

So, I'd suggest using a bit more advanced queries using variables. Without the need to join the table to itself this typically runs pretty fast:

SELECT * FROM (
SELECT
yt.*,
TIMESTAMPDIFF(second, @prevTS, `timestamp`) AS timedifference,
@prevTS:=yt.`timestamp`
FROM
yourTable yt
, (SELECT @prevTS:=(SELECT MIN(`timestamp`) FROM yourTable)) vars
ORDER BY ID
)subquery_alias
WHERE timedifference > 65

• see it working live in sqlfiddle

To further improve this query to display the two rows where timedifference is too big shouldn't be a problem :) When you get in serious trouble, feel free to ask, though.

Combine several rows with timestamps into one row with start and end timestamps

MySql 8.0 using row_number() window function to match out/in

select usr,asset
  , max(case event when 'out' then ts end) out_time
  , max(case event when 'in' then ts end) in_time
from (
  select f.*,
      -- first 'out' matches first 'in' and so on
      row_number() over(partition by usr, asset, event order by ts) as grp
  from foo f
  ) t
group by usr, asset, grp
order by out_time 

Db fiddle

MySQL timestamp differences between two rows large table

To get a result like the one it looks like you're expecting, I will sometimes make use of MySQL user-defined variables, and have MySQL perform the processing of the rows "in order", so I can compare the current row to values from the previous row.

For this to run efficiently, we'll want an appropriate index, to avoid an expensive "Using filesort" operation. (We're going to need the rows in company_id order, then by id order, so those will be the first two columns in the index. While we're at it, we might just as well include the created_at column and make it a covering index.

... ON Transactions (company_id, id, created_at)

Then we can try a query like this:

SELECT t.diff
     , t.company_id
  FROM (
         SELECT IF(r.company_id = @pv_company_id, r.created_at - @pv_created_at, NULL) AS diff
              , IF(r.company_id = @pv_company_id, 1, 0) AS include_
              , @pv_company_id := r.company_id          AS company_id
              , @pv_created_at := r.created_at          AS created_at
           FROM (SELECT @pv_company_id := NULL, @pv_created_at := NULL) i
          CROSS
           JOIN Transactions r
          ORDER
             BY r.company_id
              , r.id
       ) t
 WHERE t.include_

The MySQL Reference Manual explicitly warns against using user-defined variables like this within a statement. But the behavior we observe in MySQL 5.1 and 5.5 is consistent. (The big problem is that some future version of MySQL could use a different execution plan.)

The inline view aliased as i is just to initialize a couple of user-defined variables. We could just as easily do that as a separate step, before we run our query. But I like to include the initialization right in the statement itself, so I don't need a separate SELECT/SET statement.

MySQL accesses the Transactions table, and processes the ORDER BY first, ordering the rows from Transactions in (company_id,id) order. (We prefer to have this done via an index, rather than via an expensive "Using filesort" operation, which is why we want that index defined, with company_id and id as the leading columns.

The "trick" is saving the values from the current row into user-defined variables. When processing the next row, the values from the previous row are available in the user-defined variables, for performing comparisons (is the current row for the same company_id as the previous row?) and for performing a calculation (the difference between the created_at values of the two rows.

Based on the usage of the subtraction operation, I'm assuming that the created_at columns is integer/numeric. That is, I'm assuming that created_at is not DATE, DATETIME, or TIMESTAMP datatype, because we don't use the subtraction operation to find a difference.

SELECT a
     , b
     , a - b                  AS `subtraction`
     , DATEDIFF(a,b)          AS `datediff`
     , TIMESTAMPDIFF(DAY,b,a) AS `tsdiff`
  FROM ( SELECT DATE('2015-02-17') AS a
              , DATE('2015-01-16') AS b
       ) t

returns:

a           b           subtraction  datediff  tsdiff  
----------  ----------  -----------  --------  ------
2015-02-17  2015-01-16          101        32      32

(The subtraction operation doesn't throw an error. But what it returns may be unexpected. In this example, it returns the difference between two integer values 20150217 and 20150116, which is not the number of days between the two DATE expressions.)

EDIT

I notice that the original query includes an ORDER BY. If you need the rows returned in a specific order, you can include that column in the inline view query, and use an ORDER BY on the outer query.

SELECT t.diff
     , t.company_id
  FROM (
         SELECT IF(r.company_id = @pv_company_id, r.created_at - @pv_created_at, NULL) AS diff
              , IF(r.company_id = @pv_company_id, 1, 0) AS include_
              , @pv_company_id := r.company_id          AS company_id
              , @pv_created_at := r.created_at          AS created_at
              , r.id                                    AS id
           FROM (SELECT @pv_company_id := NULL, @pv_created_at := NULL) i
          CROSS
           JOIN Transactions r
          ORDER
             BY r.company_id
              , r.id
       ) t
 WHERE t.include_
 ORDER BY t.id

Sorry, there's no getting around a "Using filesort" for the ORDER BY on the outer query.

Time difference between adjacent rows in one column of one mysql table

If you are running MySQL 8.0, you can just use lag(). Say you want the difference in seconds:

select t.*,
    timestampdiff(
        second, 
        lag(timestamp) over(partition by eventtype order by id),
        timestamp
    ) diff
from mytable t

In earlier versions, one alternative is a correlated subquery:

select t.*,
    timestampdiff(
        second, 
        (select timestamp from mytable t1 where t1.eventtype = t.eventtype and t1.id < t.id order by t1.id desc limit 1),
        timestamp
    ) diff
from mytable t

Calculate the time difference between two timestamps in mysql

You could use the native TIMESTAMPDIFF function :

SELECT TIMESTAMPDIFF(<INTERVAL>,<timestampFrom>,<timestampTo>);

If you want to find the difference between the first and the last timestamp of a given host ID, here you are:

SELECT TIMESTAMPDIFF(MINUTE,MIN(pingtime),MAX(pingtime))
FROM yourTable
WHERE host = 2;

---

Related Topics