Need help with MySQL grouping

[DrJohnZoidberg]

I cannot seem to get this one figured out fully.

I need to group records together that fall within a certain time period, for instance all the records that were added within an hour of each other.

Let's say I have theses records:

USERNAME POINTS DATESTAMP
USER1    50     2014-01-16 23:59:03
USER1    22     2014-01-17 00:01:04
USER1    16     2014-01-17 00:19:17
USER1    11     2014-01-31 15:12:00
USER1    34     2014-01-31 15:19:34

I want to now get these grouped so it should look like this:

USERNAME POINTS
USER1    88
USER1    45

As you can see the first three records all fall within an hour of each other and the last two also do but on a different day.

Grouping by day wouldn't work, because the one date is on the previous day.

Any help would be appreciated.

 

[_kabal_]

GROUP BY DATE_FORMAT(date_field, '%Y%m%d%H')?? That format probably isn't 100%

 

[CamiKaze]

http://stackoverflow.com/questions/5002661/how-to-group-time-by-hour-or-by-10-minutes

 

[DrJohnZoidberg]

GROUP BY DATE_FORMAT(date_field, '%Y%m%d%H')?? That format probably isn't 100%

Thanks, but that doesn't solve the issue because I'll still end up with two separate groupings for each day and hour:

201401623
201401700

My Google-fu is off, cannot seem to find anything on how to do this.

 

[DrJohnZoidberg]

http://stackoverflow.com/questions/5002661/how-to-group-time-by-hour-or-by-10-minutes

That's for MSSQL, DATEPART function doesn't exist on MySQL.

 

[Messugga]

Thanks, but that doesn't solve the issue because I'll still end up with two separate groupings for each day and hour:

201401623
201401700

My Google-fu is off, cannot seem to find anything on how to do this.

So don't use the day parameter in the format, or won't that work for you?

 

[DrJohnZoidberg]

So don't use the day parameter in the format, or won't that work for you?

Nope, won't work because it will then group it by month:

My results would then look like this:

USERNAME POINTS
USER1    133

 

[Messugga]

Nope, won't work because it will then group it by month:

My results would then look like this:

USERNAME POINTS
USER1    133

Not if you leave the hour parameter...

 

[DrJohnZoidberg]

I'm thinking this a lot more complicated than I initially thought :/

 

[DrJohnZoidberg]

Not if you leave the hour parameter...

I'm realising now they way I want it creates problems. It needs to know where the starting point is somehow, I need to figure that out before I carry on.

Will update when I get something.

 

[Messugga]

Do you want to group by hour or what?

 

[prod]

Use a temp table to insert username & points that qualify the time condition, then group select from that

 

[DrJohnZoidberg]

Do you want to group by hour or what?

No, I don't want to group by hour. I need to group records that fall within an hour time range of each other. Simply grouping by hour won't work because one record can be added 13:59 and the other at 14:02, different hours but only a few minutes apart.

 

[DrJohnZoidberg]

Use a temp table to insert username & points that qualify the time condition, then group select from that

Thanks. Think I may have found something here: http://stackoverflow.com/questions/6551214/mysql-group-by-datetime-3-seconds

Will update thread when I get something right.

 

[Messugga]

Yeah that's not going to be as simple. If you have 3 records, you can have 2 pairs within an hour of each other.

 

[DrJohnZoidberg]

Yeah that's not going to be as simple. If you have 3 records, you can have 2 pairs within an hour of each other.

Yep. I need to figure this out somehow . Thanks for the input so far.

 

[koeksGHT]

try

SELECT *, sum(points) from blabla group by HOUR(date)

 

[SBSP]

You could probably make use of the previous and next record and do a calculation using something like DATE_SUB then group on the result.

But how must it know which record to use as the "Base/Starting" point.
I.E
USER1 50 2014-01-16 23:10:03
USER1 22 2014-01-17 00:01:04
USER1 16 2014-01-17 00:59:17

You will end up with
USER1,72
USER1,32

So record 2's points is now shared with record 1 and 3.


Record 3 is < 1 hour compared to record 2 but > than 1 hour compared to record 1.

 

[Bar0n]

Yep. I need to figure this out somehow . Thanks for the input so far.

You need to think 2-dimensional. Set up a matrix of times. If you want, I can see later if I can figure this out in SAS.

 

[Bar0n]

How many records should we expect in this database with the points?