Update time based on previous records in MySQL

Question

I have the following set of records:

+------------------------------------------+
| ID  | DateTimeValue       | UsedMinutes  |
| 1   | 2016-01-01 00:00:00 | 30           |
| 2   | 2016-01-01 00:00:00 | 45           |
| 3   | 2016-01-01 00:00:00 | 20           |
| 4   | 2016-02-02 00:00:00 | 30           |
| 5   | 2016-02-02 00:00:00 | 60           |
+------------------------------------------+

I would like to transform them to:

+------------------------------------------+
| ID  | DateTimeValue       | UsedMinutes  |
| 1   | 2016-01-01 00:00:00 | 30           |
| 2   | 2016-01-01 00:30:00 | 45           |
| 3   | 2016-01-01 01:15:00 | 20           |
| 4   | 2016-02-02 00:00:00 | 30           |
| 5   | 2016-02-02 00:30:00 | 60           |
+------------------------------------------+

So per date, the time value should be incremented based on the previous record and its 'Used minutes' field. My actual record set has thousands of records, so manually modifying is not an option.

Is it possible to do this with a (single) MySQL query?

Answer 1

You can give it a try:

SELECT 
t.ID,
t.expectedTime,
t.usedMin
FROM 
(
   SELECT
        ID,
        IF(@currentDateTime <> DateTimeValue, @runningTime :='0000-00-00 00:00:00' , 1) resetting,
        IF(@runningTime = '0000-00-00 00:00:00', DateTimeValue, @runningTime) AS expectedTime,
        @minuteToAddNext := UsedMinutes AS usedMin,
        @currentDateTime := DateTimeValue AS previousDateTime,
        @runningTime := IF(@runningTime = '0000-00-00 00:00:00', DateTimeValue + INTERVAL UsedMinutes MINUTE , @runningTime + INTERVAL UsedMinutes MINUTE) AS nextRowsValue
    FROM
            datetimetable
    CROSS JOIN (
            SELECT
                    @currentDateTime := '0000-00-00 00:00:00',
                    @minuteToAddNext := 0,
                    @runningTime := '0000-00-00 00:00:00'
    ) AS var
    ORDER BY DateTimeValue
) AS t;

WORKING DEMO outputs 4 columns(one extra)

UPDATED WORKING DEMO outputs 3 columns to match your requirement

---

Explanation:

While scanning each row store the UsedMinutes value in a variable called @minuteToAddNext like in the query:

@minuteToAddNext := UsedMinutes

While scanning the next row your first task is to add the @minuteToAddNext value to the DateTimeValue only if the @minuteToAddNext value was saved from the same dateTimeValue. This is translated in the query like this:

DateTimeValue + INTERVAL IF(@currentDateTime = DateTimeValue, @minuteToAddNext , 0) MINUTE AS updatedDteTimeValue

Look, if the previous row's @currentDateTime = current row's DateTimeValue then you will get @minuteToAddNext from IF. (Means same dateTimeValue).

Otherwise IF will return 0. So, DateTimeValue + INTERVAL 0 MINUTE = DateTimeValue

EDIT:

In order to update the table with those generated datetime values from the above select query you need to run the following query:

UPDATE datetimetable DT 
INNER JOIN 
(
        SELECT
        ID,
        IF(@currentDateTime <> DateTimeValue, @runningTime :='0000-00-00 00:00:00' , 1) resetting,
        IF(@runningTime = '0000-00-00 00:00:00', DateTimeValue, @runningTime) AS expectedTime,
        @minuteToAddNext := UsedMinutes AS usedMin,
        @currentDateTime := DateTimeValue AS previousDateTime,
        @runningTime := IF(@runningTime = '0000-00-00 00:00:00', DateTimeValue + INTERVAL UsedMinutes MINUTE , @runningTime + INTERVAL UsedMinutes MINUTE) AS nextRowsValue
    FROM
            datetimetable
    CROSS JOIN (
            SELECT
                    @currentDateTime := '0000-00-00 00:00:00',
                    @minuteToAddNext := 0,
                    @runningTime := '0000-00-00 00:00:00'
    ) AS var
    ORDER BY DateTimeValue
) AS t
ON DT.ID = t.ID 
SET DT.DateTimeValue = t.expectedTime;

Answer 2

I think this will solve your problem

mysql> select * from TRE;
+------+---------------+-------------+
| ID   | DateTimeValue | UsedMinutes |
+------+---------------+-------------+
|    1 | 2016-01-01    |          30 |
|    2 | 2016-01-01    |          45 |
|    3 | 2016-01-01    |          20 |
|    4 | 2016-02-02    |          30 |
|    5 | 2016-02-02    |          60 |
+------+---------------+-------------+
5 rows in set (0.00 sec)

mysql> select TRE.*,@curRank := @curRank + UsedMinutes as SUM_time from TRE,(SELECT @curRank := 0) r ORDER BY ID;
+------+---------------+-------------+----------+
| ID   | DateTimeValue | UsedMinutes | SUM_time |
+------+---------------+-------------+----------+
|    1 | 2016-01-01    |          30 |       30 |
|    2 | 2016-01-01    |          45 |       75 |
|    3 | 2016-01-01    |          20 |       95 |
|    4 | 2016-02-02    |          30 |      125 |
|    5 | 2016-02-02    |          60 |      185 |
+------+---------------+-------------+----------+
5 rows in set (0.03 sec)

mysql> select DATE_ADD(DateTimeValue, INTERVAL @curRank := @curRank + UsedMinutes MINUTE) from TRE,(SELECT @curRank := 0) r ORDER BY ID;
+-----------------------------------------------------------------------------+
| DATE_ADD(DateTimeValue, INTERVAL @curRank := @curRank + UsedMinutes MINUTE) |
+-----------------------------------------------------------------------------+
| 2016-01-01 00:30:00                                                         |
| 2016-01-01 01:15:00                                                         |
| 2016-01-01 01:35:00                                                         |
| 2016-02-02 02:05:00                                                         |
| 2016-02-02 03:05:00                                                         |
+-----------------------------------------------------------------------------+
5 rows in set (0.05 sec)