CODEWITHSUNDEEP
mysqlsql
Bitcoin Murderous Maniac
Bitcoin Murderous Maniac

Reputation: 1478

Calculating time difference of every other row from a table

Note: The data for my question is on SQLFiddle right here where you can query it.

How the table is created

I have data from a table and put into a temp table using the below logic but the BETWEEN start and end date time stamps are dynamically generated based on other logic in the stored proc, etc.

SET @RowNum = 0;
DROP TEMPORARY TABLE IF EXISTS temp; 
CREATE TEMPORARY TABLE temp AS

SELECT @RowNum := @RowNum + 1 RowNum
        , TimeStr
        , Value        
FROM mytable     
WHERE TimeStr BETWEEN '2018-01-31 06:15:56' AND '2018-01-31 19:27:09'
AND iQuality = 3 ORDER BY TimeStr;

This gives me a temp table with the row number which increments up one number in order starting with the oldest based TimeStr records, so the oldest is the time of the first record or RowNum 1.

Temp Table

enter image description here

The Data

You can get to this temp table data and play with the queries here on the SQLFiddle I've created but I have a few things I tried there you'll see there which don't give me what I need though.

Attempt to Clarify Further

I need to get the time for each ON and OFF set based on the TimeStr values in each set and I can get this using the TIMEDIFF() function.

I'm having a hard time figuring out how to make it give me the result of each ON and OFF record. The records are always in order from oldest to newest and the row number always starts at 1 too.

I some how need to give give every two records with one after the other RowNum values wise a matching CycleNum starting at 1 and increment by one per each ON and OFF cycle or set.

I can use TIMEDIFF(MAX(TimeStr), MIN(TimeStr)) as duration but I'm not sure how to best get it to group every two RowNum records in order as explained to give each set a subsequent CycleNum value that increments.

Expected Output

The expected output show look like the below screen shot for all ON and OFF cycles or every two RowNum in groups and sequence.

enter image description here

Output Clarification

I need the output to include each ON and OFF cycle's start time, end time, and the duration for the time between the start and stop.

enter image description here

Upvotes: 0

Views: 105

Answers (3)

iSR5
iSR5

Reputation: 3498

Here is a simpler one :

SELECT
  t1.TimeStr AS StartTime, 
  t2.TimeStr AS EndTime, 
  TIMEDIFF(t2.TimeStr, t1.TimeStr) AS Duration  
FROM temp t1
INNER JOIN temp t2 ON t2.RowNum = t1.RowNum + 1
WHERE 
    t2.Value = 0
AND t1.Value = 1

Upvotes: 1

KIKO Software
KIKO Software

Reputation: 16688

A quick and dirty way to do it would be this:

SELECT 
  T1.TimeStr AS StartTime, 
  (SELECT T2.TimeStr FROM temp AS T2 WHERE T2.RowNum = T1.RowNum+1) AS StopTime,
  TIMEDIFF((SELECT T2.TimeStr FROM temp AS T2 WHERE T2.RowNum = T1.RowNum+1),
           T1.TimeStr) AS Duration
FROM temp AS T1
WHERE Value = 1;

Seems like there must be better ways to do this. Two subqueries will be slow.

You could do it in two steps:

CREATE TEMPORARY TABLE startstop AS
SELECT 
  T1.TimeStr AS StartTime, 
  (SELECT T2.TimeStr FROM temp AS T2 WHERE T2.RowNum = T1.RowNum+1) AS StopTime,
  0 AS Duration
FROM temp AS T1
WHERE Value = 1;

UPDATE startstop SET Duration = StopTime - StartTime;

However I cannot test this in the Fiddle.

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269773

If you can guarantee two things:

  • That the row numbers are strictly sequential with no gaps.
  • That the on/off flag is always alternating.

Then you can do this with a relatively simple join. The code looks like:

SELECT (@rn := @rn + 1) as cycle, t.*, tnext.timestr,
       timediff(tnext.timestr, t.timestr)
FROM temp t JOIN
     temp tnext
     ON t.rownum = tnext.rownum - 1 and
        t.value = 1 and
        tnext.value = 0 cross join
     (SELECT @rn := 0) params;

If these conditions are not true, then more complex logic is needed.

Upvotes: 3

Related Questions