6

I'm new to this forum. I've been having trouble constructing a MySQL query. Basically I want to select data and use some sort of function to output the timestamp field in a certain way. I know that dateformat can do this by minute, day, hour, etc. But consider the following: Say it is 12:59pm. I want to be able to select data from the past day, and have the data be placed into two hour wide time 'bins' based on it's timestamp.
So these bins would be: 10:00am, 8:00am, 6:00am, 4:00am, etc, and the query would convert the data's timestamp in one of these bins. E.G. data converted 4:45am becomes 4:00am, 6:30am becomes 6:00am, 9:55am becomes 8:00am, 10:03am becomes 10:00am, 11:00am becomes 10:00am

Make sense? The width of the bins needs to be dynamic as well. I hope I described the problem clearly, and any help is appreciated.

OMG Ponies
  • 325,700
  • 82
  • 523
  • 502
Tucker
  • 61
  • 1
  • Bins can be done with [`INTERVAL`](http://dev.mysql.com/doc/refman/5.0/en/comparison-operators.html#function_interval) or just with `FLOOR`. A `GROUP BY FLOOR(hour/binsize)*binsize` should get you started. – Konerak Feb 21 '11 at 19:16
  • Cool, thanks. I'd also like to do this by month too where a binsize wouldn't necessarily be static. – Tucker Feb 21 '11 at 19:39

2 Answers2

1

Examples:

Monthly buckets:

GROUP BY YEAR(datestampfield) desc, MONTH(datestampfield) desc

Hourly buckets, with number of hours configurable:

set @rangehrs = 2; select *,FLOOR(HOUR(dateadded)/@rangehrs )*@rangehrs as x from mytable GROUP BY FLOOR(HOUR(dateadded)/@rangehrs )*@rangehrs limit 5;

servermanfail
  • 2,532
  • 20
  • 21
0

Sounds like you're looking for a histogram of time. Not sure that's a real thing, but the term histogram might get you in a good place....like this related question:

Getting data for histogram plot

Community
  • 1
  • 1
Jody
  • 8,021
  • 4
  • 26
  • 29