Mysql Datetime queries

Question

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.

Bins can be done with INTERVAL or just with FLOOR. A GROUP BY FLOOR(hour/binsize)*binsize should get you started. — Konerak
– Konerak, Commented Feb 21, 2011 at 19:16
Cool, thanks. I'd also like to do this by month too where a binsize wouldn't necessarily be static. — Tucker
– Tucker, Commented Feb 21, 2011 at 19:39

servermanfail · Accepted Answer · 2011-02-22 10:05:51Z

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;

answered Feb 22, 2011 at 10:05

servermanfail

2,53820 silver badges21 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Community · Accepted Answer · 2017-05-23 12:04:11Z

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

edited May 23, 2017 at 12:04

CommunityBot

11 silver badge

answered Feb 27, 2011 at 17:19

Jody

8,3614 gold badges29 silver badges29 bronze badges

Collectives™ on Stack Overflow

Mysql Datetime queries

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related