How to make this UPDATE?

0 like 0 dislike
3 views
In General, the database has records, each record has a field dt that contains a Timestamp of the time the record was added.

Example:
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 | 3 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
by

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,186 answers
0 comments
33,905 users