mysql - How I can get the first row of each hour? -


mysql []> show columns reading;

| field      | type       | null | key | default |          | | cont       | int(4)     | no   | pri | null    | auto_increment | | id         | int(4)     | yes  | mul | null    |                | | sensor_num | int(4)     | yes  |     | null    |                | | value      | float(5,2) | yes  |     | null    |                | | time       | datetime   | yes  |     | null    |                | 

select * reading;

  18570 |    1 |          1 | 23.93 | 2017-08-14 12:45:01 | | 18571 |    1 |          1 | 23.93 | 2017-08-14 12:46:01 | | 18572 |    1 |          1 | 23.93 | 2017-08-14 12:47:01 | | 18573 |    1 |          1 | 23.93 | 2017-08-14 12:48:01 | | 18574 |    1 |          1 | 23.93 | 2017-08-14 12:49:02 | | 18575 |    1 |          1 | 23.93 | 2017-08-14 12:50:02 | | 18576 |    1 |          1 | 23.93 | 2017-08-14 12:51:02 | | 18577 |    1 |          1 | 23.93 | 2017-08-14 12:52:02 | | 18578 |    1 |          1 | 23.93 | 2017-08-14 12:53:02 | | 18579 |    1 |          1 | 23.93 | 2017-08-14 12:54:02 | | 18580 |    1 |          1 | 23.93 | 2017-08-14 12:55:02 | | 18581 |    1 |          1 | 23.93 | 2017-08-14 12:56:02 | | 18582 |    1 |          1 | 23.93 | 2017-08-14 12:57:02 |  | 18689 |    1 |          1 | 25.88 | 2017-08-14 14:54:56 | | 18690 |    1 |          1 | 25.88 | 2017-08-14 14:55:56 | | 18691 |    1 |          1 | 25.88 | 2017-08-14 14:56:56 | | 18692 |    1 |          1 | 25.88 | 2017-08-14 14:57:56 | | 18693 |    1 |          1 | 25.88 | 2017-08-14 14:58:56 | | 18694 |    1 |          1 | 25.88 | 2017-08-14 14:59:56 | | 18695 |    1 |          1 | 25.88 | 2017-08-14 15:00:56 | | 18696 |    1 |          1 | 25.88 | 2017-08-14 15:01:56 | | 18697 |    1 |          1 | 25.88 | 2017-08-14 15:02:56 | | 18698 |    1 |          1 | 25.88 | 2017-08-14 15:03:56 | | 18699 |    1 |          1 | 25.88 | 2017-08-14 15:04:56 | | 18700 |    1 |          1 | 25.88 | 2017-08-14 15:05:56 | 

if want first rows each hour, can select rows match min(time) group hour:

  select *    reading   inner join (     select date(time) t_date, min(time) t_time     reading      group date(time), hour(time)   ) t on  t.t_time = a.time 

Comments

Popular posts from this blog

android - InAppBilling registering BroadcastReceiver in AndroidManifest -

python Tkinter Capturing keyboard events save as one single string -

sql server - Why does Linq-to-SQL add unnecessary COUNT()? -