Only select from db if datetime is less than 1 hour from current datetime?

PHP
1

I’m trying to make an sql call where only to select from db if the date time from dbtable (2012-04-02 04:04:32) is less than 1 hour difference from the current time? I want to be able to do so within my sql query but not sure how?

Please help.

2

I have come up with this, but still no luck… Any ideas?

$sql = "SELECT UNIX_TIMESTAMP(timetodraw) newtime, UNIX_TIMESTAMP() nowtime, game_id FROM ".$prefix."_games WHERE UNIX_TIMESTAMP(timetodraw) < UNIX_TIMESTAMP(3600)";

What I want is to only get records that are within one hour of the current time.

3

I think this should work. You get the current time as UNIX_TIMESTAMP with the php function time(). Take 1 hour off (3600).

With between you say, UNIX_TIMESTAMP(timetodraw), must be bigger then time()-3600 and smaller then time().

$sql = "SELECT UNIX_TIMESTAMP(timetodraw) newtime, UNIX_TIMESTAMP() nowtime, game_id FROM ".$prefix."_games WHERE UNIX_TIMESTAMP(timetodraw) BETWEEN ".(time()-3600)." AND ".time();

OR if you are looking an hour ahead

$sql = "SELECT UNIX_TIMESTAMP(timetodraw) newtime, UNIX_TIMESTAMP() nowtime, game_id FROM ".$prefix."_games WHERE UNIX_TIMESTAMP(timetodraw) BETWEEN ".time()." AND ".(time()+3600);
4 
SELECT timetodraw        AS newtime
     , CURRENT_TIMESTAMP AS nowtime
     , game_id 
  FROM ".$prefix."_games 
 WHERE timetodraw 
       BETWEEN CURRENT_TIMESTAMP - INTERVAL 1 HOUR
           AND CURRENT_TIMESTAMP

or, alternatively…

 WHERE timetodraw 
       BETWEEN CURRENT_TIMESTAMP 
           AND CURRENT_TIMESTAMP + INTERVAL 1 HOUR

depending on what you meant by “within” :cool:

5

@jmansa,

Try and adapt this function that is used to show the records viewed in the last 24 hours ie > yesterday.

Special Note: field type ‘date’ is TIMESTAMP




//============================================
public function today_hits_get_links()
{
  // Delete first if the MONTH
  $yesterday = date('Y-m-d H:i',   time() - 24*60*60);

  $query = "
    SELECT  id, hits, xrl, title, memo
    FROM    jokes
    WHERE   date >= '" .$yesterday ."'
    ORDER   BY hits
    DESC
    LIMIT   0, 20";
  $query = $this->db->query($query);

  $i2 = 1;
  $result_today = array();

  if($query)
  {
    foreach($query->result() as $row):
      $result_today[] = $this->_get_links_with_memo_in_title($row);
    endforeach;
    $query->free_result();
  }

  return $result_today;
}
6