Menu
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.
856
MySQL retrieves and displays DATETIME values in ' YYYY-MM-DD hh:mm:ss ' format. The supported range is '1000-01-01 00:00:00' to '9999-12-31 23:59:59' . The TIMESTAMP data type is used for values that contain both date and time parts. TIMESTAMP has a range of '1970-01-01 00:00:01' UTC to '2038-01-19 03:14:07' UTC.
In the MySQL documentation shows the following example: CREATE TABLE t1 ( ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP, dt DATETIME DEFAULT CURRENT_TIMESTAMP );
You can use DATE() from MySQL to select records with a particular date. The syntax is as follows. SELECT *from yourTableName WHERE DATE(yourDateColumnName)='anyDate'; To understand the above syntax, let us first create a table.
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;
78
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
