--- a/common.inc.php +++ b/common.inc.php @@ -1,6 +1,7 @@ <?php date_default_timezone_set("Australia/Sydney"); $basePath = ""; +$DATA_DIR = "./data"; /** Returns the offset from the origin timezone to the remote timezone, in seconds. * @param $remote_tz; @@ -22,9 +23,8 @@ } function getSensorValuesByHour($sensorID, $timeFrom, $timeTo) { - $sql = 'select min(time) as time, min(value), max(value), avg(value), stddev(value) from sensor_values where sensor_id = ? - and extract(epoch from time) > ? and extract(epoch from time) < ? - group by sensor_id, date_trunc(\'hour\', time) order by time'; + $sql = 'select min(call_timestamp) as time, count(*), min(length), max(length), avg(length), stddev(length) from recordings + group by tgid, date_trunc(\'hour\', call_timestamp) order by time'; $query = $this->db->query($sql, Array($sensorID, $timeFrom, $timeTo)); return $query->result_array(); @@ -39,7 +39,7 @@ } function getSensorDataYears($sensorID, $timeFrom, $timeTo) { - $sql = "select distinct extract('year' from time) as year from sensor_values where sensor_id = ? order by year"; + $sql = "select distinct extract('year' from call_timestamp) as year from recordings where tgid = ? order by year"; $query = $this->db->query($sql, Array($sensorID)); return $query->result_array();