0

I have a table 'mytable' like this

ID | dateTime            |     data      | 
------------------------------------------
1  | 2013-09-01 00:15:00 |   some data   |
2  | 2013-09-01 00:15:00 |   some data   |
4  | 2013-09-01 00:15:00 |   some data   |
5  | 2013-09-01 00:30:00 |   some data   |
6  | 2013-09-01 00:30:00 |   some data   |
7  | 2013-09-01 00:30:00 |   some data   |
8  | 2013-09-01 00:45:00 |   some data   |
9  | 2013-09-01 00:45:00 |   some data   |
10 | 2013-09-01 00:45:00 |   some data   |

I was fine before but I accidentally changed the dateTime to round to 15 minutes (I was supposed to round it for 5 minutes) please refere to this, No I want to round the time for 5 minutes.

I think the only way I can do this, is to get the dateTime of one record then check the record in the next row, if both are same then add 5 minutes into it. How do I get the value of the next row and compare it with previous one?

Can anyone help me with this??

Thanks

Community
  • 1
  • 1
Ana
  • 584
  • 2
  • 6
  • 16
  • You have four entries at 00:30:00. Doing what you suggest will give you entries at 00:30, 00:35, 00:40, and 00:45 - and you already have entries at 00:45. You need to restore your last backup (you _do_ have a backup?) and carry out the update again. –  Oct 10 '13 at 11:04
  • I do not have a backup :(... but this is not an issue... I have only 2 repeating values not 3 ... i will update my question... please share you answer if you can help me... – Ana Oct 10 '13 at 11:07

1 Answers1

0

This query will output the new datetime values, where I add 5 minutes every time the datetime is the same as the previous row, ordered by Id:

SELECT ID, 
    DATE_ADD(dateTime, INTERVAL 5 * (ID - 
        (SELECT MIN(ID) FROM MyTable T2 WHERE T2.dateTime = T1.dateTime)
    ) MINUTE) AS dateTime,
    data
)
FROM MyTable T1

It works by adding 5 times the difference in ID values between the current row and the first row (minimum ID) of the same dateTime value.

While this will definitely do what you want, depending on how you rounded down to 15 minutes, the output will not necessarily be the same as if you had rounded down to 5 minutes from your original data. Your best option would be to restore from a backup.

Dan
  • 10,480
  • 23
  • 49
  • Great... thanks for you answer... it works fine for me... I can select the dateTime as I required but I am struggling to updated the dateTime... it gives me an error `Error Code: 1093. You can't specify target table 'mytable' for update in FROM clause` – Ana Oct 10 '13 at 11:41
  • I suggest that you use my query above to populate a temporary table with the updated values. Then you can update the original table from the temporary table. – Dan Oct 10 '13 at 12:03