Display Comments from Last 24-Hours

Is there any difference between these two SQL statements…


	$whereClause = 'AND ac.created_on >= DATE_SUB(CURDATE(), INTERVAL 1 DAY)';


	$whereClause = 'AND ac.created_on >= DATE_SUB(CURDATE(), INTERVAL 24 HOUR)';

Sincerely,

Debbie

no

sincerely…

I don’t think I worded this thread correctly. :blush:

If I want to show all Comments from the last 24 hours, then would the following SQL snippets below yield the same results??


	$whereClause = 'AND ac.created_on >= DATE_SUB(CURDATE(), INTERVAL 1 DAY)';


	$whereClause = 'AND ac.created_on >= DATE_SUB(CURDATE(), INTERVAL 24 HOUR)';

Sincerely,

Debbie

let’s begin with a question…

what is the datatype of your ac.created_on column?

DATETIME

thank you

all your base are belong to us

next question…

by “last 24 hours” do you mean in the previous 24 counting backwards from exactly right now?

or in the 24-hour period up to but not including the hour that we’re currently in? (5pm - 6pm eastern daylight)???

or in the 24-hour period which was yesterday (since today is not over yet)

you can see where i’m going, right debbie?

the answer to your question depends entirely on your exact question

You’re welcome.

Huh??

Lemme answer it this way and you help me find the right question and answer…

(As you know) People can post Comments beneath Articles on my website. And I am adding the ability to Sort and Filter these Comments.

Coming up with Sorting options was easy for me.

Filtering isn’t as intuitive as most people think - especially in this particular case.

So for now, I figured these Filters might be useful to people…


- None
- Editor's Choice
- Last Day
- Last 7 Days

The intent of the last two options if to help users get to what has recently been said.

When I say “Last Day”, I guess I was thinking, “Show me just the Comments which have been posted from right now and counting backwards 24 hours (i.e. Last 24-Hours)”

So, it is currently Wednesday, August 13 @ 6:07pm EDT. If I chose the second to last filter, I would want to see all Comments from right now back to Tuesday, August 13 @ 6:08pm EDT.

Sincerely,

Debbie

P.S. If you think something else would be more useful to users, feel free to speak up.

excellent reply, debbie… no sarcasm intended or implied… seriously :slight_smile:

your description of the user’s experience really helps

use INTERVAL 24 HOURS

or however many hours is in 7 days…:wink:

Woo hoo! I’m getting better! :slight_smile:

Glad to hear.

I’m not following how this code will work…


	$whereClause = 'AND ac.created_on >= DATE_SUB(CURDATE(), INTERVAL 24 HOUR)';

It is now Wednesday, August 13 @ 6:38pm EDT

So, CURDATE() just returns "2014-08-13’, right?

My query should "Go back 24 hours from ‘2014-08-13 18:48:00’ ", but doesn’t the time portion get chopped off?

It seems like CURDATE and INTERVAL 24 HOURS have incompatible time units… :-/

Don’t I need something that keeps the DATE + TIME before I subtract off 24 hours?

Sincerely,

Debbie

replace CURDATE() with CURRENT_TIMESTAMP

Using CURDATE() seemed wrong to me in this situation, so I created a Test Table and some Teat Data.

(Do I get any points for that, Rudy??) :slight_smile:

In this particular case INTERVAL 1 DAY vs. INTERVAL 24 HOUR makes no difference.

What was crucial is that CURDATE() was not the right choice.

I tried NOW() and things seems to meet my requirements above.

Either of these will do as far as I can see…


SELECT *
FROM z_test
WHERE created_on >= DATE_SUB(NOW(), INTERVAL 1 DAY)


SELECT *
FROM z_test
WHERE created_on >= DATE_SUB(NOW(), INTERVAL 24 HOUR)

Sincerely,

Debbie

did you try what i suggested?

That appears to yield the same results.

Debbie

Maybe consider changing the fieldtype to TimeStamp which is a lot easier to extract information using PHP date(…) Function?