SitePoint Sponsor

User Tag List

Results 1 to 12 of 12
  1. #1
    SitePoint Guru
    Join Date
    Aug 2004
    Location
    Earth
    Posts
    739
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Date older than xx days

    Hey,

    Trying to grab database records are older than 14 days.. In the database its stored as date/time.

    I am wanting to check only on the date part of the field, so when the cron job runs through daily it only grabs records that are 14 days older than today.

    I tried a few methods like:

    Code:
    WHERE DATE(timeslot) = DATE(NOW(), INTERVAL 14 DAY)
    Which generates an error?

    Thanks

  2. #2
    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)
    timeslots 14 days ago --
    Code:
    WHERE timeslot >= CURRENT_DATE - INTERVAL 14 DAY
      AND timeslot  < CURRENT_DATE - INTERVAL 13 DAY
    timeslots 14 days and older --
    Code:
    WHERE timeslot < CURRENT_DATE - INTERVAL 14 DAY
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Guru
    Join Date
    Aug 2004
    Location
    Earth
    Posts
    739
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks.. I did have something like that to start.. but started thinking that I could do it using equal to apposed to where/and..

  4. #4
    SitePoint Guru
    Join Date
    Aug 2004
    Location
    Earth
    Posts
    739
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Doesnt appear to bring back any results, I have put record in database that is 14 days prior to todays date..

  5. #5
    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)
    doing it with an equality requires using the DATE() function, to extract just the date portion

    but the problem with using a function is that it requires a table scan, whereas the date range (lower and upper bounds) allows an index to be used

    if you could show your query, and dump the table and a few rows, i'll double-check your query for you
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  6. #6
    SitePoint Guru
    Join Date
    Aug 2004
    Location
    Earth
    Posts
    739
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    My bad.. it does work.. The server is on a different time zone, so a day or so out..

  7. #7
    SitePoint Guru
    Join Date
    Aug 2004
    Location
    Earth
    Posts
    739
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Cant seem to find a way to alter the query so that..

    Bring back results that are 30 days old only if there are no entries within the last 30 days..

  8. #8
    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 _matrix_ View Post
    Bring back results that are 30 days old only if there are no entries within the last 30 days..
    use two queries

    first query gets results within the last 30 days, and if there are none, then second query gets results older than 30 days
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  9. #9
    SitePoint Guru
    Join Date
    Aug 2004
    Location
    Earth
    Posts
    739
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hmm, i'm still lost on it.. This is for appointments.. If 30 days has passed since last appointment dispatch email, but exclude anyone that has another appointment booked between 30 days ago and into the future.. if that makes sence?

    The code below sends out to anyone who had an appointment logged 30 days ago, even if they have made another appointment for after that..

    Code:
    $sql = mysql_query("SELECT t.*, c.*
    FROM timeslots as t
    JOIN clients as c ON c.clientid = t.clientid
    WHERE timeslot >= CURRENT_DATE - INTERVAL 31 DAY
    AND timeslot  < CURRENT_DATE - INTERVAL 30 DAY");

  10. #10
    SitePoint Member
    Join Date
    Jan 2012
    Posts
    12
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Perhaps no one had an appointment on 31st August.

  11. #11
    SitePoint Guru
    Join Date
    Aug 2004
    Location
    Earth
    Posts
    739
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    An example of results of the above script (these people had an appointment 30 days ago, so would be emailed..)

    Fri 31 Aug 2012 @ 8:30 am -> Alan
    Fri 31 Aug 2012 @ 12:30 pm -> Martin
    Fri 31 Aug 2012 @ 3:00 pm -> Craig
    Fri 31 Aug 2012 @ 9:00 am -> Sue
    Fri 31 Aug 2012 @ 2:00 pm -> Kellie
    Fri 31 Aug 2012 @ 9:30 am -> Karen


    But these 2 names allready have up coming appointments..

    Thu 13 Sep 2012 @ 1:00 pm -> Kellie
    Fri 14 Sep 2012 @ 3:30 pm -> Martin

    So need to be excluded from the results above.. Basically looking for people that havnt been back in the last 30 days and havnt booked any further appointments.. A note saying we havnt seen you in awhile, if you would like to make another appointment, call.....

  12. #12
    SitePoint Guru
    Join Date
    Aug 2004
    Location
    Earth
    Posts
    739
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    The answer:

    Code:
    $sql = mysql_query("SELECT c.clientid, c.email, c.name, MAX(DATE(t.timeslot)) as latest 
    FROM timeslots as t
    JOIN clients as c ON c.clientid = t.clientid
    GROUP BY c.clientid
    HAVING MAX(DATE(timeslot)) = CURDATE() - INTERVAL 30 DAY");
    Maybe helpfull to someone else..


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
  •