SQL hour averaging in 24hours

Question

Hi I'm trying to do sql query based on hourly averaging in 24hours temperature with the sample table below. How can I do so? Let me know if you think this high frequencies tables architecture will face difficulties in future too. Appreciate for any helps. Thank you.

_id     Node_ID Modality    Timestamp       Value
------- ------- ----------- ------------    ------
4930376 2002    Temperature 24/8/15 6:25    30
4930377 2403    Temperature 24/8/15 6:24    30
4930378 2004    Temperature 24/8/15 6:21    30
4930379 2203    Temperature 24/8/15 6:26    29
4930380 2211    Temperature 24/8/15 6:21    29
4930381 2401    Temperature 24/8/15 6:25    33
4930382 2411    Temperature 24/8/15 6:24    30
4930383 2101    Temperature 24/8/15 6:22    31
4930384 2201    Temperature 24/8/15 6:27    30
4930385 2111    Temperature 24/8/15 6:30    36
4930386 2113    Temperature 24/8/15 6:27    35
4934082 3200    Temperature 24/8/15 10:28   33
4934083 2402    Temperature 24/8/15 10:30   32
4934084 2213    Temperature 24/8/15 10:30   33
4934085 2103    Temperature 24/8/15 10:34   36
4934086 2423    Temperature 24/8/15 10:32   32
4934087 2421    Temperature 24/8/15 10:34   32
4934088 2413    Temperature 24/8/15 10:37   32
4934089 2102    Temperature 24/8/15 10:31   35
4934090 2202    Temperature 24/8/15 10:35   32
4934091 2404    Temperature 24/8/15 10:33   35
4934092 2121    Temperature 24/8/15 10:39   36
4934093 2002    Temperature 24/8/15 10:35   33
4940370 2123    Temperature 24/8/15 17:44   36
4940371 2422    Temperature 24/8/15 17:40   33
4940372 2001    Temperature 24/8/15 17:36   35
4940373 2301    Temperature 24/8/15 17:39   32
4940374 2402    Temperature 24/8/15 17:36   32
4940375 2103    Temperature 24/8/15 17:40   36
4940376 2423    Temperature 24/8/15 17:38   33
4940377 2421    Temperature 24/8/15 17:40   33

Thanks kitty, how do you made it to remove the indentation? – Eric T Aug 25 '15 at 03:21 — Eric T, Aug 25 '15 at 03:21
What results do you want? – Gordon Linoff Aug 25 '15 at 03:27 — Gordon Linoff, Aug 25 '15 at 03:27
Hourly averaging from this 24 hours temperature table. – Eric T Aug 25 '15 at 03:28 — Eric T, Aug 25 '15 at 03:28

score 1 · Accepted Answer · answered Aug 25 '15 at 03:21

1

SELECT AVG(value), _id, Node_ID, Modality, value, Timestamp
    FROM yourTable
    GROUP BY DATE(Timestamp), HOUR(Timestamp);

answered Aug 25 '15 at 03:21

asdf

2,927
2
21
42

SQL hour averaging in 24hours

1 Answers1

Linked