SitePoint Sponsor

User Tag List

Results 1 to 7 of 7
  1. #1
    SitePoint Guru
    Join Date
    Aug 2004
    Location
    Taunton, UK
    Posts
    787
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Calling a query with now() minus one day

    My client is reporting that the following query is not displaying the results right up until the end of the day (UK)

    $query = "SELECT * from auctionstb where date >= now() ORDER BY date ASC";

    Is there any reason this would happen?

    Anyway as a solution I am seeking assistance how to call the same query but instead of now() I want to use now() - 1

    So it would basically display all results from yesterday onwards.

    Any advice / help appreciated.

    Thanks

    Paul
    Mediakitchen Limited
    App Development | Website Design & Development | Flash Game Development
    Somerset, UK
    http://www.mediakitchen.co.uk

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,216
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    what datatype is the column? how are values inserted into that column?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Guru
    Join Date
    Aug 2004
    Location
    Taunton, UK
    Posts
    787
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    The datatype is date and here is how I add date to the database

    $theyear = $_POST['select_year'];
    $themonth = $_POST['select_month'];
    $theday = $_POST['select_day'];
    $thedate = $theyear . ":" . $themonth . ":" . $theday;

    $query = "INSERT INTO auctionstb(date, time, location, description, filename)
    VALUES('$thedate', '$thetimeslot', '$thelocation','$thedescription','$cataloguename')";
    Mediakitchen Limited
    App Development | Website Design & Development | Flash Game Development
    Somerset, UK
    http://www.mediakitchen.co.uk

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,216
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    okay, the first thing you want to do is start using the CURRENT_DATE function instead of NOW(), because NOW() includes time

    what did you want again? auctions that started yesterday or later?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Guru
    Join Date
    Aug 2004
    Location
    Taunton, UK
    Posts
    787
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks!

    Basically each auction has a date when it is on. The auction should display on the site all day on this particular date however the client is reporting that they are not displaying all day and disappearing before the end of the day.

    As a solution I therefore wanted to display them for one extra day.

    So yes I would like to display auctions whose date is any date after and including yesterday.

    Many thanks!

    Paul
    Mediakitchen Limited
    App Development | Website Design & Development | Flash Game Development
    Somerset, UK
    http://www.mediakitchen.co.uk

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,216
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    i have a feeling you were using a hammer to try to kill a fly

    NOW() includes a time potion

    so, for example, NOW() + INTERVAL 24 HOUR is actually 2008-05-30 14:09:37

    however, your stored dates would be like 2008-05-29

    at some point in the day, it only makes sense that an event would stop showing

    it all depends on how you write your WHERE clause

    NOW() is mysql's synonym for the standard sql CURRENT_TIMESTAMP function, and includes both a date and a time

    perhaps you should be using CURRENT_DATE in your comparisons?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SitePoint Guru
    Join Date
    Aug 2004
    Location
    Taunton, UK
    Posts
    787
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks r937 - I have now changed the relevant code to use CURRENT_TIMESTAMP instead.

    So my code is now as follows:

    Code:
    $query = "SELECT * from auctionstb where date >= CURRENT_DATE ORDER BY date ASC";
    Many thanks for your help on this one!
    Mediakitchen Limited
    App Development | Website Design & Development | Flash Game Development
    Somerset, UK
    http://www.mediakitchen.co.uk


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
  •