SitePoint Sponsor

User Tag List

Results 1 to 5 of 5
  1. #1
    SitePoint Guru
    Join Date
    Dec 2005
    Posts
    964
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    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. #2
    SitePoint Guru
    Join Date
    Dec 2005
    Posts
    964
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I have come up with this, but still no luck... Any ideas?
    PHP Code:
    $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. #3
    SitePoint Enthusiast
    Join Date
    Apr 2008
    Location
    The Netherlands
    Posts
    44
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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().

    PHP Code:
    $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

    PHP Code:
    $sql "SELECT UNIX_TIMESTAMP(timetodraw) newtime, UNIX_TIMESTAMP() nowtime, game_id FROM ".$prefix."_games WHERE UNIX_TIMESTAMP(timetodraw) BETWEEN ".time()." AND ".(time()+3600); 

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,220
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by jmansa View Post
    What I want is to only get records that are within one hour of the current time.
    Code:
    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...
    Code:
     WHERE timetodraw 
           BETWEEN CURRENT_TIMESTAMP 
               AND CURRENT_TIMESTAMP + INTERVAL 1 HOUR
    depending on what you meant by "within"
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Mentor bronze trophy
    John_Betong's Avatar
    Join Date
    Aug 2005
    Location
    City of Angels
    Posts
    1,807
    Mentioned
    73 Post(s)
    Tagged
    6 Thread(s)
    @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

    PHP Code:


    //============================================
    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;



Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •