/** * Calculate valid timestamp boundaries for aggregation table usage * * table: --data-- -----aggregate----- -data- * timestamp: from ... aggFrom ..... aggTo ... to * * @param string $type aggregation level (e.g. 'day') * @return boolean true: aggregate table contains data, aggFrom/aggTo contains valid range * @author Andreas Goetz <*****@*****.**> */ private function getAggregationBoundary($aggFromDelta = null) { $dateFormat = Util\Aggregation::getAggregationDateFormat($this->aggLevel); // day = "%Y-%m-%d" // aggFrom becomes beginning of first period with aggregate data $sqlParameters = array($this->channel->getId(), $this->aggType, $this->from); if (isset($aggFromDelta)) { // shift 'left' border of aggregate table use by $aggFromDelta units $sql = 'SELECT UNIX_TIMESTAMP(' . 'DATE_ADD(' . 'FROM_UNIXTIME(MIN(timestamp) / 1000, ' . $dateFormat . '), ' . 'INTERVAL ' . $aggFromDelta . ' ' . $this->aggLevel . ')) * 1000 ' . 'FROM aggregate WHERE channel_id=? AND type=? AND ' . ' UNIX_TIMESTAMP(FROM_UNIXTIME(timestamp / 1000, ' . $dateFormat . ')) * 1000 >=?'; } else { // find 'left' border of aggregate table after $from $sql = 'SELECT UNIX_TIMESTAMP(FROM_UNIXTIME(MIN(timestamp) / 1000, ' . $dateFormat . ')) * 1000 ' . 'FROM aggregate WHERE channel_id=? AND type=? AND ' . ' UNIX_TIMESTAMP(FROM_UNIXTIME(timestamp / 1000, ' . $dateFormat . ')) * 1000 >=?'; } $this->aggFrom = $this->conn->fetchColumn($sql, $sqlParameters, 0); $this->aggTo = null; // aggregate table contains relevant data? if (isset($this->aggFrom)) { // aggTo becomes beginning of first period without aggregate data $sqlParameters = array($this->channel->getId(), $this->aggType); $sql = 'SELECT UNIX_TIMESTAMP(' . 'DATE_ADD(' . 'FROM_UNIXTIME(MAX(timestamp) / 1000, ' . $dateFormat . '), ' . 'INTERVAL 1 ' . $this->aggLevel . ')) * 1000 ' . 'FROM aggregate WHERE channel_id=? AND type=?'; if (isset($this->to)) { $sqlParameters[] = $this->to; $sql .= ' AND timestamp<?'; } $this->aggTo = $this->conn->fetchColumn($sql, $sqlParameters, 0); } if (self::$debug) { printf("from .. aggFrom .. aggTo .. to\n"); printf("%s |%s .. %s| %s\n", self::pd($this->from), self::pd($this->aggFrom), self::pd($this->aggFrom), self::pd($this->to)); } return isset($this->aggFrom) && isset($this->aggTo) && $this->aggFrom < $this->aggTo && $this->from <= $this->aggFrom && $this->aggTo <= $this->to; }