CODEWITHSUNDEEP
mysql
user2647541
user2647541

Reputation: 77

mysql query to select data per hour

I am trying to build a query to get data per hour for a particular day(or today). I have a device data table that 

#   Name                      Type      
    1  : idPrimary  --       int(11)                
    2 : inputDate   --      varchar(32) 
    3 : input1      --      varchar(11)     
    4 : input2      --      varchar(11)

And in inputDate my data in this format :

SELECT inputDate FROM `deviceData` WHERE `inputDate` BETWEEN '2015-05-29 16:30:07' AND '2015-05-29 21:30:07' ORDER BY id

Showing rows 0 - 24 (3304 total, Query took 0.3487 seconds.) 

2015-05-29 16:30:09
2015-05-29 16:30:20
2015-05-29 16:30:25
2015-05-29 16:30:41
2015-05-29 16:30:46
2015-05-29 16:30:51
2015-05-29 16:30:56
2015-05-29 16:31:01
2015-05-29 16:31:07
2015-05-29 16:31:49
2015-05-29 16:31:54
2015-05-29 16:32:00
2015-05-29 16:32:10
2015-05-29 16:32:15
Now I want to take only one data in a hour, The minutes and seconds isn't important.For example :

2015-05-29 5:*:*
2015-05-29 6:*:*
2015-05-29 7:*:*
2015-05-29 8:*:*

I don't know the best way to do this. I use regex but i can't do this. Thanks

Upvotes: 1

Views: 1365

Answers (2)

Gevorg M
Gevorg M

Reputation: 51

select max(inputDate) from deviceData group by date(inputDate),hour(inputDate);

I think this must be helpful.

Upvotes: 0

Dmitry V.
Dmitry V.

Reputation: 336

Well, this is hideous, but. Try this query :)

select * from (select substr(inputDate, 1, 13) as hours, input1 from deviceData order by inputDate desc) t GROUP BY hours;

I might ve messed up with sorting direction, though/

Upvotes: 3

Related Questions