November 7th, 2013, 12:54 PM
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:
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:
But what about the date, which changes every day?
...WHERE `created` > 23:59:59");
November 7th, 2013, 01:46 PM
First create a time value:
In your where clause (assuming that your field is of type datetime:
$currdt = date("Y-m-d");
$dttm = strtotime("$currdt 00:00:00");
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.
where UNIX_TIMESTAMP(your_date_field) > $dttm
Comments on this post
November 7th, 2013, 02:20 PM
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?
DATE(created) = CURRENT_DATE()
November 7th, 2013, 02:49 PM
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.
Originally Posted by Jacques1
November 7th, 2013, 02:56 PM
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:
This keeps the query logic within the querying language, cuts down on inter-language dependencies, and makes the queries cleaner.
SELECT * FROM comments WHERE theID = 123 AND postedDate < DATE_FORMAT(NOW(), '%Y-%m-%d 00:00:00');
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.
November 7th, 2013, 03:37 PM
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.
November 7th, 2013, 10:35 PM
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
November 8th, 2013, 07:42 AM
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.
November 12th, 2013, 06:38 AM
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