#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2003
    Posts
    3
    Rep Power
    0

    Selecting records based on a future datetime


    Hello,

    I am attempting to create a simple scheduling project and have a MySQL table with a
    datetime field (I can change to timestamp if necessary).

    In any case that specific field called starttime contains a future date and time (appointment) say 20 days
    in the future.

    I need to create a script that I can run as a cron job that will select all records that are between 15 minutes
    to 20 minutes in the future from the current date and time so I can send an alert message of the upcoming
    appointment is in 15 minutes.

    I am not successful in the attempts I have made: here is the latest attempt at retrieving records:
    $r1 = date("Y-m-d H:i:s", strtotime('+15 minutes'));
    $r2 = date("Y-m-d H:i:s", strtotime('+15 minutes'));
    $sql = "SELECT * FROM wp_appointments WHERE starttime >= $r1 AND starttime < $r2";

    and I also tried:
    $sql = "SELECT * FROM wp_appointments WHERE starttime >= DATE_ADD(NOW(), INTERVAL 15 MINUTE) AND starttime < DATE_ADD(NOW(), INTERVAL 20 MINUTE)";

    I would appreciate any help in getting this sql query working correctly.

    Thanks,
    Kim
  2. #2
  3. Lazy Moderator
    Devshed Supreme Being (6500+ posts)

    Join Date
    Mar 2007
    Location
    Washington, USA
    Posts
    16,437
    Rep Power
    9645
    Dates in MySQL work like strings Dates in MySQL queries are written with quotes just like how strings are, so with the first form you tried you would need to put quotes around the date values.
    PHP Code:
    $sql "SELECT * FROM wp_appointments WHERE starttime >= '$r1' AND starttime < '$r2'"
    (and your $r2 does +15 minutes when it should be +20)

    The second form should have worked. Did it fail? What was the error message? Or did it work but not find any results?
    Last edited by requinix; June 11th, 2016 at 10:56 AM. Reason: I had sacrificed accuracy for conciseness >_>
  4. #3
  5. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,734
    Rep Power
    4288
    Originally Posted by requinix
    Dates in MySQL work like strings
    not at all...

    ... unless you are talking about how to specify a date value as a literal constant -- yes, that's done with strings

    but dates in MySQL work like ~dates~ for cryin out loud, and deserve to be treated with date functions, not string functions

    Comments on this post

    • requinix agrees
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  6. #4
  7. Lazy Moderator
    Devshed Supreme Being (6500+ posts)

    Join Date
    Mar 2007
    Location
    Washington, USA
    Posts
    16,437
    Rep Power
    9645
    Yeah, okay, poor choice of words there I was thinking just about how to write the values.
  8. #5
  9. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,734
    Rep Power
    4288
    sorry if i was harsh, just one of my pet peeves
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

IMN logo majestic logo threadwatch logo seochat tools logo