Page 1 of 1

please help limit unix_timestamp by last 7 days

Posted: Thu Nov 17, 2011 10:40 am
by chris666uk1
please could some one help me i need to limit the query by timestamp for the last 7 days here is my code on the php side

Code: Select all

 <?php
			 
             $db = @mysql_connect("localhost", "8conv", "*****") or die("Connection Error: " . mysql_error());
             mysql_select_db("tempmonitor") or die("Error connecting to db.");
             $sql = "SELECT *, UNIX_TIMESTAMP(datetime) AS datetime FROM 8conv";
             $result = mysql_query($sql);
             $data = array();
             while ($row = mysql_fetch_array($result)) {
                $temp1[] = array($row['datetime'] * 1000, (int)$row['temp1']);;
				
             }
            
			 $temp1 = json_encode($temp1);

Re: please help limit unix_timestamp by last 7 days

Posted: Thu Nov 17, 2011 10:54 am
by Celauran

Code: Select all

$dt = new DateTime("7 days ago");
$limit = $dt->format('Y-m-d H:i:s');
$query = "SELECT foo FROM bar WHERE datetime > {$limit}";

Re: please help limit unix_timestamp by last 7 days

Posted: Thu Nov 17, 2011 3:26 pm
by pickle
Better to do the date math in MySQL.

Add a WHERE clause like:

Code: Select all

WHERE
  `datetime` > DATE_SUB(NOW(), INTERVAL 7 DAY)