SitePoint Sponsor

User Tag List

Results 1 to 7 of 7
  1. #1
    SitePoint Addict xDev's Avatar
    Join Date
    Jul 2003
    Location
    Moncton, New Brunswick, Canada
    Posts
    247
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    The last seven days

    Here's my table structure:

    CREATE TABLE news (
    id int(10) unsigned NOT NULL auto_increment,
    title varchar(255) NOT NULL default '',
    url varchar(255) NOT NULL default '',
    body text,
    newsdate timestamp(14) NOT NULL,
    PRIMARY KEY (id),
    FULLTEXT KEY title (title,url,body)
    ) TYPE=MyISAM;

    How can I get a result with all cols for an interval of the last seven days. Maybe this is simple but after looking over the available date functions I still can't get what I want. Maybe its because I used timestamp, I don't know.

    Thanks,
    -xDev

  2. #2
    Put your best practices away. The New Guy's Avatar
    Join Date
    Sep 2002
    Location
    Canada
    Posts
    2,087
    Mentioned
    1 Post(s)
    Tagged
    1 Thread(s)
    timestamp is the way to go. Basically timestamps are always increasing numbers (I think, or atleast I make mine as such). So you subtract the current timestamp(today) minus the timestamp in the database. If the number is <= to 7 days worth, then display.
    "A nerd who gets contacts
    and a trendy hair cut is still a nerd"

    - Stephen Colbert on Apple Users

  3. #3
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,248
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    Code:
    select id 
         , title 
         , url 
         , body 
         , newsdate
      from news
     where newsdate
           between date_add(current_date, interval -7 day) 
               and current_date
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  4. #4
    SitePoint Addict xDev's Avatar
    Join Date
    Jul 2003
    Location
    Moncton, New Brunswick, Canada
    Posts
    247
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks for the code. It's working, sort of, but I'm getting funny results. I'll have to test it some more, I might need your help again. If I attach my sql dump would you be willing to try your query on my data to see what's up? I only have 200 rows, I get 59 which seems about right but the results don't go all the way up to my posts I made today.

  5. #5
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,248
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    thanks for the feedback

    change the last line to
    Code:
    and date_add(current_date, interval 1 day)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  6. #6
    SitePoint Addict xDev's Avatar
    Join Date
    Jul 2003
    Location
    Moncton, New Brunswick, Canada
    Posts
    247
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks! Works great

    Code:
    SELECT id, title, url, body, newsdate
    FROM news
    WHERE newsdate
    BETWEEN date_add( current_date,
    INTERVAL - 7
    DAY ) AND date_add( current_date,
    INTERVAL 1
    DAY )
    ORDER BY newsdate DESC
    So how come the interval is -7 instead of 7? The way I read up on it on the mysql site the first example they posted was this:

    Code:
    mysql> SELECT something FROM tbl_name
        -> WHERE DATE_SUB(CURDATE(),INTERVAL 30 DAY) <= date_col;
    I tried to use that example and changed it to interval 7 but it wasn't working.

    Thanks,
    -xDev

  7. #7
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,248
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    how come the interval is -7?

    because i was using date_add()
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


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
  •