SitePoint Sponsor

User Tag List

Results 1 to 10 of 10
  1. #1
    SitePoint Guru SharifTK's Avatar
    Join Date
    Jan 2004
    Location
    New York, NY
    Posts
    621
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Selecting Records Based On Time!

    How would I go on about selecting a record between a given time period in unix timestamp format and the current time?

    For example:

    $old_time = 1057106550;
    $new_time = time();

    How would I get any records made between the $old_time and $new_time vars? Note that my tables has a datetime field in it called "timestamp". Thanks.
    SK

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Code:
    select foo
      from yourtable
     where `timestamp` 
           between from_unixtime(old_time) 
                 and now()
    note: this uses mysql's NOW() function
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Guru SharifTK's Avatar
    Join Date
    Jan 2004
    Location
    New York, NY
    Posts
    621
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Ok let me just post what exactly i am trying to do:

    I have 4 tables:

    news:
    id (int), timestamp (datetime)

    articles:
    id (int), timestamp (datetime)

    reviews:
    id (int), date_reviewed (date)

    previews:
    id (int), date_previewed (date)

    I am basically trying to get all records created in theses tables between one given time and the current time.

    My complete code:
    PHP Code:
    $sql "
    SELECT 
    t1.id, t2.id, t3.id, t4.id 
    FROM 
    news t1, articles t2, reviews t3, previews t4 
    WHERE 
    UNIX_TIMESTAMP(t1.timestamp) BETWEEN FROM_UNIXTIME('1087950019') AND now() 
    AND 
    UNIX_TIMESTAMP(t2.timestamp) BETWEEN FROM_UNIXTIME('1087950019') AND now() 
    AND 
    UNIX_TIMESTAMP(t3.date_reviewed) BETWEEN FROM_UNIXTIME('1087950019') AND now() 
    AND 
    UNIX_TIMESTAMP(t4.date_previewed) BETWEEN FROM_UNIXTIME('1087950019') AND now()"
    ;
    $query mysql_query($sql);

    if(
    mysql_num_rows($query) != 0)
    {
    echo 
    'there are new records';

    Does that explain better?
    SK

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    WHERE
    UNIX_TIMESTAMP(t1.timestamp) <-- converts a datetime value into a unix timestamp value
    BETWEEN FROM_UNIXTIME('1087950019') <-- converts a unix timestamp value into a datetime value
    AND now() <-- a datetime value

    so you should leave the t1.timestamp as a datetime value

    did i mention that your choice of column name may be misleading
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Guru SharifTK's Avatar
    Join Date
    Jan 2004
    Location
    New York, NY
    Posts
    621
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Well it looks like your query and my query is no different. So I tried mine again and again, I inserted a new record in the news table and tried to see if any rows were returned, but nothing!
    SK

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    okay, let's get to the bottom of this

    do a dump with phpmyadmin or whatever tool you have

    i would like to have the CREATE TABLE statement, plus a few sample rows of data

    then i can test the sql and determine exactly where it's not working
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SitePoint Guru SharifTK's Avatar
    Join Date
    Jan 2004
    Location
    New York, NY
    Posts
    621
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Sure, actually I think it would be better if I just do one table instead of 4 because it's just unnecassary to create a one severely complex query when 4 simple could do. Here is the table dump:
    Code:
    CREATE TABLE news (
      id int(11) NOT NULL auto_increment,
      timestamp datetime NOT NULL default '0000-00-00 00:00:00',
      authorid int(11) NOT NULL default '0',
      title text NOT NULL,
      teaser text NOT NULL,
      article text NOT NULL,
      icon_id int(11) NOT NULL default '0',
      PRIMARY KEY  (id)
    ) TYPE=MyISAM;
    SK

  8. #8
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    OH MY $DEITY

    i am so sorry

    i just looked at your query in detail for the first time

    your problem is in the data, not the sql

    and yes, now that you mention it, it would be better to combine those tables

    let me know when you've done that and then let's try the sql again afterwards
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  9. #9
    SitePoint Guru SharifTK's Avatar
    Join Date
    Jan 2004
    Location
    New York, NY
    Posts
    621
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I fixed it. Just did 4 seperate queries. And did not use the FROM_UNIXTIME().
    SK

  10. #10
    SitePoint Guru SharifTK's Avatar
    Join Date
    Jan 2004
    Location
    New York, NY
    Posts
    621
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Nope didn't work! It shows ALL records instead...
    SK


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
  •