Using MySQL : Compare values in two consecutive records in a table using a timestamp field

Question

i have a table tableData

(startTime, endTime, data)

For example,

startTime               |            endTime             |        data  
2000-01-01 03:00:00     |    2000-01-01 03:00:10         |         88   
2000-01-01 03:00:10     |    2000-01-01 03:00:20         |         33  
2000-01-01 03:00:30     |    2000-01-01 03:00:40         |         67  ......and so on

i would like the entry(2000-01-01 03:00:20,2000-01-01 03:00:30,0) to be inserted in data whenever there is no data , hence 0. the output would be :-

 startTime              |           endTime                 |       data  
2000-01-01 03:00:00     |    2000-01-01 03:00:10            |      88   
2000-01-01 03:00:10     |    2000-01-01 03:00:20            |      33    
**2000-01-01 03:00:20   |    2000-01-01 03:00:30            |       0**  
2000-01-01 03:00:30     |    2000-01-01 03:00:40            |      67  .....  and so on

What is the easiest possible way to do this.
Please i need it done by today or my boss is gonna kill me. Thanks guys.

Is there any way to check if the endTime of the previous row is equal to the startTime of the current row and insert into the table accordingly?

MORE INFO :-

This is the stored procedure im using now to accumulate the from the inputTable and store it into the output table using 10 second intervals. http://sqlfiddle.com/#!2/838de/1

[What have you tried?](http://www.whathaveyoutried.com/) See [ask advice](http://stackoverflow.com/questions/ask-advice), please. — John Conde, Jan 24 '13 at 14:45
Im new to databases so I unsuccessfully tried to create a cursor in a stored procedure, but i cant seem to go back to the previous row to compare — rohan23, Jan 24 '13 at 14:54
http://stackoverflow.com/questions/9177177/how-to-insert-a-row-in-a-table-between-two-existing-rows-in-sql — Anda Iancu, Jan 24 '13 at 15:08
but there are thousands of entires and no id, the entries come dynamically as and when required — rohan23, Jan 24 '13 at 15:26
You give this as example data, but is it always 10 second intervals in your actual table? — Jodaka, Jan 24 '13 at 15:34
Is there any chance that a new record can have a timestamp (in either column) lower than the highest present in the table? (so that the slug record might need to be replaced) — inhan, Jan 24 '13 at 15:57
Also, why is this not a trigger for the case *[before insert]*? — inhan, Jan 24 '13 at 16:05
no the table is fixed, a new table will be derived if new records or time interval is needed. — rohan23, Jan 24 '13 at 16:06
the insert is done using a group by clause and unix_timestamp() conversion... i dont have the exact code since i cant take data out of the office. — rohan23, Jan 24 '13 at 16:08
Your question still needs so much clarification. And you shouldn't be using the comments to answer these. You need to edit your question accordingly. Also, you might want to show your own effort for us to help on your own approach. — inhan, Jan 24 '13 at 16:09

score 0 · Answer 1 · answered Jan 24 '13 at 15:11

0

just insert the record and create a view where

SELECT * FROM tableData ORDER BY startTime ASC, endTime ASC;

answered Jan 24 '13 at 15:11

Anda Iancu

530
1
3
9

no thats it tableData ( startTime timestamp, endTime timestamp, data int) , i think i could add a auto - increment primary key if required – rohan23 Jan 24 '13 at 15:46
There is no easy way. One dummy test will be to add a column preferred_Order and use a trigger to update the values. – Anda Iancu Jan 24 '13 at 16:27
Dummy test will be to add a column preferred_Order and use a trigger to update the values. Why not just use ORDER BY clause? i.e. why are you interested in the physical row order? – Anda Iancu Jan 24 '13 at 16:33
http://sqlfiddle.com/#!2/43365/1 - test data (just because i like to work with real data) – Anda Iancu Jan 24 '13 at 16:39
thanks Anda, i need it to be shown that there is 0 data in that time interval for analysis done at a later stage by the team. so ive been asked to add the physical row. – rohan23 Jan 24 '13 at 16:51
hence i need the row to be inserted. oh thanks for the test data! – rohan23 Jan 24 '13 at 16:51

score 0 · Answer 2 · answered Jan 25 '13 at 06:13

Here's an example.

I'm assuming each new record is greater than or equal to the greatest endTime in the table and each record advances by 10 seconds. I'm also assuming that you'll never be inserting multiple rows in a single call.

Otherwise you will still need to give more information regarding how you want to handle possibilities.

-- DROP PROCEDURE IF EXISTS addRecord;
DELIMITER //
CREATE PROCEDURE addRecord(startT TIMESTAMP, endT TIMESTAMP, rowData INT)
BEGIN
    DECLARE lastEndTime TIMESTAMP;
    SET lastEndTime = (SELECT MAX(`endTime`) FROM tableData);
    WHILE lastEndTime < startT DO
        INSERT INTO tableData (`startTime`,`endTime`,`data`)
        VALUES (lastEndTime,DATE_ADD(lastEndTime, INTERVAL 10 SECOND),0);
        SET lastEndTime = DATE_ADD(lastEndTime, INTERVAL 10 SECOND);
    END WHILE;
    INSERT INTO `tableData` (`startTime`,`endTime`,`data`)
    VALUES (startT,endT,rowData);
END //

To call this…

CALL addRecord('2000-01-01 03:00:50','2000-01-01 03:01:00',23);

Hey inhan, Thanks for your suggestion!, but I can't seem to figure out how to input data into the output table one at a time. I've added the stored procedure I'm using in the edited question. Thanks again. — rohan23, Jan 27 '13 at 13:11

Using MySQL : Compare values in two consecutive records in a table using a timestamp field

2 Answers2