CURDATE()

[The Little Guy]

Im adding a part "Todays Hot Products"

 

This will display the day's Hot Products, or the most commonly viewed products for the day.

 

In the viewed_products table, I have a date column, that has a date formatted like: 2007-02-28 10:33:26

I want to get this portion: 2007-02-28 and then it doesn't matter what comes after that.

 

This is the line that Does that

WHERE VIEWED_PRODUCTS.DATE = '(CURDATE()%,INTERVAL 1 DAY)'

Is it correct?

 

Here is the full SQL

SELECT *,COUNT(PRODUCT)
FROM VIEWED_PRODUCTS
INNER JOIN IMAGES
INNER JOIN PRODUCTS 
ON
PRODUCTS.PRODUCT_NUM = VIEWED_PRODUCTS.PRODUCT
WHERE VIEWED_PRODUCTS.DATE = '(CURDATE()%,INTERVAL 1 DAY)'
GROUP BY VIEWED_PRODUCTS.DATE LIMIT 3

[fenway]

I don't know what you think that does... especially the %.

[bwochinski]

I think you're missing a "DATE_SUB" on there.  I assume the "%" is meant to do some kind of wildcard match against the time portion.  That isn't what is needed. 

 

There are 2 ways to do something like this, depending on what you're after.  You can get all rows with a date equal to yesterday:

DATE(viewed_products.date) = DATE_SUB(CURDATE(),INTERVAL 1 DAY)

 

Or you could get all rows from within the last 24 hours:

viewed_products.date > DATE_SUB(NOW(),INTERVAL 1 DAY)

 

Check out the functions I used here on the mysql docs: http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html

[fenway]

Best to keep the column name as the lvalue, so that an index can be used if necessary... you can also use CURDATE - INTERVAL 1 DAY, which I find cleaner.

[bwochinski]

I can understand that... makes the query more difficult though.  Is there a better way to accomplish the "datetimes from yesterday" than this? -->

viewed_products.date BETWEEN ((CURDATE()+" 0:00:00") - INTERVAL 1 DAY) AND ((CURDATE()+" 23:59:59") - INTERVAL 1 DAY)

[fenway]

How about

 

date BETWEEN ( CURDATE() - INTERVAL 1 DAY ) AND CURDATE()

The date->time type conversation should be automatic... though you might get midnight for the 2nd part.

[bwochinski]

Ah yeah that's nice , fogot about the implied "00:00:00".

 

So that's the way you should do it, Little Guy.  Heh.

[The Little Guy]

OK... Its giving me results, the bad thing is... I don't know what results I am looking for now.

 

For me to know this is for me to get more people, Its really hard to do this with only me viewing.

[fenway]

Huh?

[The Little Guy]

basically I'm trying to get the total number of views of a product within a certain day, and the results that I am returning I haven't a clue if they are accurate or not, because I don't really know what I want returned.

[artacus]

Well if you don't know what you're looking for...

And did fenway slap you yet for using date as a field name? *slap*

 

Now that that is out of the way...

If you want to get products viewed today use:

WHERE DATE(VIEWED_PRODUCTS.DATE) = CURDATE()