SimonSaysCode Posted November 13, 2012 Share Posted November 13, 2012 Hello All, So I am attempting to get the previous day's data from my table, but I want it to be averaged by hour (I have an entry for every 5 minute interval). So I assumed it would look something like this: $sth = $db->prepare("SELECT AVG(Power) AS AvgPower, strftime('%H', Timestamp, 'unixepoch', 'localtime') AS TheDate FROM TestTable WHERE datetime(Timestamp, 'unixepoch') >= datetime('now', '- 24 hours') GROUP BY TheDate"); This isn't working however, so I was hoping someone could tell me where I am going wrong. Thanks! Link to comment https://forums.phpfreaks.com/topic/270642-retrieving-specific-data/ Share on other sites More sharing options...
SimonSaysCode Posted November 13, 2012 Author Share Posted November 13, 2012 Additionally, I should mention that I am using SQLite and PHP. Link to comment https://forums.phpfreaks.com/topic/270642-retrieving-specific-data/#findComment-1392109 Share on other sites More sharing options...
SimonSaysCode Posted November 15, 2012 Author Share Posted November 15, 2012 Hello All, So I am attempting to get the previous day's data from my table, but I want it to be averaged by hour (I have an entry for every 5 minute interval). So I assumed it would look something like this: $sth = $db->prepare("SELECT AVG(Power) AS AvgPower, strftime('%H', Timestamp, 'unixepoch', 'localtime') AS TheDate FROM TestTable WHERE datetime(Timestamp, 'unixepoch') >= datetime('now', '-24 hours') GROUP BY TheDate"); This isn't working however, so I was hoping someone could tell me where I am going wrong. Thanks! The space in between the - and 24 was the problem. Solved. Link to comment https://forums.phpfreaks.com/topic/270642-retrieving-specific-data/#findComment-1392509 Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.