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

    Join Date
    Feb 2009
    Posts
    45
    Rep Power
    47

    Filter out all comments before midnight of current day


    I'm working on a comment page. The date/time is generated by NOW() which, as you know, displays as, for example:

    2013-11-05 07:54:06

    How do I word my sql query to only display comments that were made since midnight? I imagine for the time it's as simple as:

    Code:
    ...WHERE `created` > 23:59:59");
    But what about the date, which changes every day?
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2013
    Posts
    189
    Rep Power
    0
    First create a time value:

    Code:
    $currdt = date("Y-m-d");
    $dttm = strtotime("$currdt 00:00:00");
    In your where clause (assuming that your field is of type datetime:

    Code:
    ....
       where UNIX_TIMESTAMP(your_date_field) > $dttm
    ....
    Basically make a datetime field out of your desired cutoff point then use a MySQL function in your query to make the comment's field into a timestamp and compare them.

    Comments on this post

    • Jacques1 disagrees
  4. #3
  5. --
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Jul 2012
    Posts
    3,959
    Rep Power
    1014
    No, no. This is really the weirdest possible approach.

    I'm not even sure what this "since midnight" is supposed to say. Aren't we simply talking about the current day?

    sql Code:
    SELECT
    	1
    FROM
    	whatever
    WHERE
    	DATE(created) = CURRENT_DATE()
    ;
    The 6 worst sins of security ē How to (properly) access a MySQL database with PHP

    Why canít I use certain words like "drop" as part of my Security Question answers?
    There are certain words used by hackers to try to gain access to systems and manipulate data; therefore, the following words are restricted: "select," "delete," "update," "insert," "drop" and "null".
  6. #4
  7. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2013
    Posts
    189
    Rep Power
    0
    Originally Posted by Jacques1
    No, no. This is really the weirdest possible approach.
    I disagree. I gave the OP something which he could use not only for "current date" alone, but which was more universal, allowing him to choose any date/time value he wanted.
  8. #5
  9. Sarcky
    Devshed Supreme Being (6500+ posts)

    Join Date
    Oct 2006
    Location
    Pennsylvania, USA
    Posts
    10,908
    Rep Power
    6351
    There's no reason to format something in one programming language (PHP) only to hand it to another (SQL) which is more than capable of doing it itself. You can even do exactly what you said, but in the actual query itself:
    Code:
    SELECT * FROM comments WHERE theID = 123 AND postedDate < DATE_FORMAT(NOW(), '%Y-%m-%d 00:00:00');
    This keeps the query logic within the querying language, cuts down on inter-language dependencies, and makes the queries cleaner.
    HEY! YOU! Read the New User Guide and Forum Rules

    "They that can give up essential liberty to obtain a little temporary safety deserve neither liberty nor safety." -Benjamin Franklin

    "The greatest tragedy of this changing society is that people who never knew what it was like before will simply assume that this is the way things are supposed to be." -2600 Magazine, Fall 2002

    Think we're being rude? Maybe you asked a bad question or you're a Help Vampire. Trying to argue intelligently? Please read this.
  10. #6
  11. --
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Jul 2012
    Posts
    3,959
    Rep Power
    1014
    And now all we have to do is figure out the difference between "the comments of today" and "the comments of today since midnight". Maybe I have a wrong understanding of the space-time continuum, but this sounds pretty identical to me -- which brings us back to #3.
    The 6 worst sins of security ē How to (properly) access a MySQL database with PHP

    Why canít I use certain words like "drop" as part of my Security Question answers?
    There are certain words used by hackers to try to gain access to systems and manipulate data; therefore, the following words are restricted: "select," "delete," "update," "insert," "drop" and "null".
  12. #7
  13. No Profile Picture
    Contributing User
    Devshed Novice (500 - 999 posts)

    Join Date
    Jun 2009
    Posts
    677
    Rep Power
    7
    And as far as the "Since midnight'... Is this meerly a since midnight to the individual viewing the page? So, with the timezones, each will be off an hour? Or since midnight in relation to the server's timezone? Either way, you need not know a single thing about time, just date.
    He who knows not and knows not he knows not: he is a fool - shun him. He who knows not and knows he knows not: he is simple - teach him. He who knows and knows not he knows: he is asleep - wake him. He who knows and knows he knows: he is wise - follow him
  14. #8
  15. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2009
    Posts
    45
    Rep Power
    47
    I've finally figured out the difference between me and the rest of you guys. You overthink things too much!!!

    All I want to do is keep the comments forum neat, displaying no more than the comments of the current day, everything after midnight last night til midnight tonight.

    Seems like Jacques1 came up with the simplest solution, which worked, even though I've completely messed with his concept of space/time continuum. You're welcome ;-)
    Last edited by mystic7; November 8th, 2013 at 07:46 AM.
  16. #9
  17. No Profile Picture
    Contributing User
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Dec 2004
    Posts
    2,990
    Rep Power
    375
    if you think people over thinking is a wrong thing then that is a wrong approach. People like jacques, requinix etc over think and WASTE their time on people like us so we will benefit. they get NOTHING out of it. and if they tell us extra things that makes our code better and we learn something extra then I dont see the problem.

    What I see as a problem is people taking advantage of people like these. For example I have made a habit of looking through all the new threads at least once each day and the number of times people have been using MYSQL instead of new extension and poor Jacques having to explain it over and over again when if you guys searched or even look at the STICKIES at the TOP of the forum would mean these experts would have a bit more time to answer important/tricky questions

    Comments on this post

    • ManiacDan agrees

IMN logo majestic logo threadwatch logo seochat tools logo