0

I logging my temperature every 1-3 seconds with unix timestamp, I wish like get row of data return by average every 1 minute period and last for 24 hour.

my logger table look like this:

unixtime    temp
1350899052  25.37
1350899054  25.44
1350899057  25.44
1350899059  25.44
1350899062  25.44
1350899064  25.44
1350899069  25.44
1350899071  25.44

and i wish like it return as

unixtime    temp
1350899052  25.37 -- average value of 1 minute range both unixtime and temp 
1350899054  25.44
1350899057  25.44

please advise how is mySQL command should i do ? thank.

Nont
  • 7
  • 3

2 Answers2

2

The following should work:

SELECT ROUND(unixtime/(60)) AS minute, avg(temp) FROM table where unixtime >= UNIX_TIMESTAMP(DATE_SUB(NOW(), INTERVAL 1 DAY)) GROUP BY ROUND(unixtime/(60));

Show date in normal format:

SELECT from_unixtime(floor(unixtime/(60))*60) AS minute, avg(temp) FROM test where unixtime >= UNIX_TIMESTAMP(DATE_SUB(NOW(), INTERVAL 1 DAY)) GROUP BY floor(unixtime/(60))

outputs:

minute  avg(temp)
2012-10-24 08:02:00 37.5
2012-10-24 08:03:00 45
Arkadiusz 'flies' Rzadkowolski
  • 5,676
  • 2
  • 29
  • 31
  • Yes, because it shows minutes from 1970. What should your output look like? 2012-10-12 00:00:01 25,777 2012-10-12 00:00:02 28,999 ? – Arkadiusz 'flies' Rzadkowolski Oct 24 '12 at 06:21
  • i got return as munite : 22517598 26.64000075204032 not unix time average as i expect, but close to how could i get return as unix time in average period, thank for help – Nont Oct 24 '12 at 06:24
  • could you explain GROUP BY ROUND(unixtime/(60)); i don't understand how it work. thank again. – Nont Oct 24 '12 at 09:19
0
 1. 1 min

select * from logger_table where unixtime> timestamp(DATE_SUB(NOW(), INTERVAL 1 MINUTE)) group by temp

 2. 24 hrs
select * from logger_table where unixtime> timestamp(DATE_SUB(NOW(), INTERVAL 24 HOUR)) group by temp;
Arun Killu
  • 13,581
  • 5
  • 34
  • 61