How to make this UPDATE?

Warning: count(): Parameter must be an array or an object that implements Countable in /home/styllloz/public_html/qa-theme/donut-theme/qa-donut-layer.php on line 274
0 like 0 dislike
In General, the database has records, each record has a field dt that contains a Timestamp of the time the record was added.

id | user | data | dt

For each user record "clumps", i.e., is for example 10 records in the interval from 20 sec to 20 min, then empty all day - following again around this time. Ie the interval between the "heap" not less than 23 hours.

Need to add another column to the table - dt_start. And here it is inside each heap should be put down minimalny Timestamp from this heap.

Tell me how to do it
by | 13 views

1 Answer

0 like 0 dislike
if correctly understood the task, it can be solved with an UPDATE + JOIN, like so:
UPDATE mytable RIGHT JOIN (SELECT `user`, MIN(dt) as dt_start, DATE(dt) as d FROM mytable GROUP BY user, d) as temp ON mytable.user = temp.user AND DATE(mytable.dt) = temp.d SET mytable.dt_start = temp.dt_start

in General, for each heap to put down the minimum value of time is not a good idea - You will have duplicate data, because each entry in the heap will be the same dt_start. If You just so clearly silhouetted heap is to make a separate table for their list, and to put down the minimum time

Related questions

0 like 0 dislike
1 answer
0 like 0 dislike
3 answers
0 like 0 dislike
3 answers
0 like 0 dislike
3 answers
110,608 questions
257,187 answers
40,796 users