[SOLVED] Unix time comparison

[wsantos]

I am trying to select records within a day interval. However the comparison of the timestamp seems not to work. uniquied is timestamp. Could anyone help me on this. Thanks

 

// Read form

  if(empty($_POST['StartDate']))
  {
    $startyear = date("Y");
    $startmonth = date("m");
    $startday = date("j");
    $endyear = date("Y");
    $endmonth = date("m");
    $endday = date("j");
    
    $strstartdate = $startyear . "-" . $startmonth . "-" . $startday . " 00:00:00";
    $strenddate = $endyear . "-" . $endmonth . "-" . $endday . " 23:59:59";
    $startdate = mktime(0,0,0,$startmonth,$startday,$startyear);
    $enddate = mktime(23,59,59,$endmonth,$endday,$endyear);
  }
  else
  {
    $pststartdate = $_POST['StartDate'];
    $pstenddate = $_POST['EndDate'];
    list($startmonth, $startday, $startyear) = split('[/.-]', $pststartdate);
    $strstartdate = $startyear . "-" . $startmonth . "-" . $startday . " " . "00:00:00";
    list($endmonth, $endday, $endyear) = split('[/.-]', $pstenddate);
    $strenddate = $endyear . "-" . $endmonth . "-" . $endday . " " . "23:59:59";
    $jd = gregoriantojd($startmonth,$startday,$startyear);
    $startdate=jdtounix($jd);
    $jd = gregoriantojd($endmonth,$endday,$endyear);
    $enddate=jdtounix($jd);
  }

// Connect to Table
  $conAsteriskCDR = mysql_connect("ip","user","password");
  if(!$conAsteriskCDR)
  {
    die('Could not connect: ' . mysql_error());
  }
  $dbAsteriskCDR = mysql_select_db("asteriskcdr",$conAsteriskCDR);

// Trying to get all activity for that given date from AsteriskCDR.
  $qryAsteriskCDR = "SELECT a.uniqueid,a.cname,q.action from anis a LEFT OUTER JOIN queue_actions q ON a.uniqueid=q.uniqueid where a.cname like '%PAF DISH G%' and from_unixtime(a.uniqueid)>" . $startdate . " limit 5";

  $result = mysql_query($qryAsteriskCDR,$conAsteriskCDR);
  if(!$result)
  {
    $message = 'Invalid query: ' . mysql_error() . '\n' . 'Whole query: ' . $qryAsteriskCDR;
    die($message);
  }

  while($data = mysql_fetch_array($result, MYSQL_ASSOC))
    $dataarr[] = $data;

// Terminate Connection
  mysql_close($conAsteriskCDR);

[Barand]

How are you storing the date in your db table?

[wsantos]

got it solved.thanks here is my solution..

 

$qryAsteriskCDR = "SELECT a.uniqueid,a.cname,q.action from anis a LEFT OUTER JOIN queue_actions q ON a.uniqueid=q.uniqueid where a.cname like '%PAF DISH G%' and from_unixtime(a.uniqueid)>=from_unixtime(" . $startdate . ") and from_unixtime(a.uniqueid)<from_unixtime(" . $enddate . ")";

 

Just wondering if there is anyway i can further speed this up. This tables contain billions of records each. Thanks