MySQL selecting yesterday's date

How can I display and count the values whose dates are yesterday? I used time() to insert date in the database. Example:

URL: google.com youtube.com google.com youtube.com test.com youtube.com
DateVisited: 1313668492 1313668540 1313668571 13154314

I want do display how man开发者_开发百科y URLs that have multiple existed in the table and also how many of that URL have been visited yesterday. Example result:

LINK       | timesExisted | timesVisitedYesterday
Google.com |       2      | 2
youtube.com|       3      | 3

I already have the idea on getting yesterday's date, but I don't have an idea on counting how many times a URL has existed for yesterday and counting how many times a URL has existed in the table.

---

The simplest and best way to get yesterday's date is subdate:

subdate(current_date, 1)

Your query would be:

SELECT 
    url as LINK,
    count(*) as timesExisted,
    sum(DateVisited between UNIX_TIMESTAMP(subdate(current_date, 1)) and
        UNIX_TIMESTAMP(current_date)) as timesVisitedYesterday
FROM mytable
GROUP BY 1

For the curious, the reason that sum(condition) gives you the count of rows that satisfy the condition, which would otherwise require a cumbersome and wordy case statement, is that in mysql boolean values are 1 for true and 0 for false, so summing a condition effectively counts how many times it's true. Using this pattern can neaten up your SQL code.

---

SELECT SUBDATE(NOW(),1);

where now() function returs current date and time of system in Timestamp...

you can use:

SELECT SUBDATE(CURDATE(),1)

---

You can use:

SELECT SUBDATE(NOW(), 1);

or

SELECT SUBDATE(NOW(), INTERVAL 1 DAY);

or

SELECT NOW() - INTERVAL 1 DAY;

or

SELECT DATE_SUB(NOW(), INTERVAL 1 DAY);

---

Last or next date, week, month & year calculation. It might be helpful for anyone.

Current Date:

select curdate();

Yesterday:

select subdate(curdate(), 1)

Tomorrow:

select adddate(curdate(), 1)

Last 1 week:

select between subdate(curdate(), 7) and subdate(curdate(), 1)

Next 1 week:

between adddate(curdate(), 7) and adddate(curdate(), 1)

Last 1 month:

between subdate(curdate(), 30) and subdate(curdate(), 1)

Next 1 month:

between adddate(curdate(), 30) and adddate(curdate(), 1)

Current month:

subdate(curdate(),day(curdate())-1) and last_day(curdate());

Last 1 year:

between subdate(curdate(), 365) and subdate(curdate(), 1)

Next 1 year:

between adddate(curdate(), 365) and adddate(curdate(), 1)

---

You can get yesterday's date by using the expression CAST(NOW() - INTERVAL 1 DAY AS DATE). So something like this might work:

SELECT * FROM your_table

WHERE DateVisited >= UNIX_TIMESTAMP(CAST(NOW() - INTERVAL 1 DAY AS DATE))
  AND DateVisited <= UNIX_TIMESTAMP(CAST(NOW() AS DATE));

---

Query for the last weeks:

SELECT *
FROM dual
WHERE search_date BETWEEN SUBDATE(CURDATE(), 7) AND CURDATE()

---

While the chosen answer is correct and more concise, I'd argue for the structure noted in other answers:

SELECT * FROM your_table
WHERE UNIX_TIMESTAMP(DateVisited) >= UNIX_TIMESTAMP(CAST(NOW() - INTERVAL 1 DAY AS DATE))
  AND UNIX_TIMESTAMP(DateVisited) <= UNIX_TIMESTAMP(CAST(NOW() AS DATE));

If you just need a bare date without timestamp you could also write it as the following:

SELECT * FROM your_table
WHERE DateVisited >= CAST(NOW() - INTERVAL 1 DAY AS DATE)
  AND DateVisited <= CAST(NOW() AS DATE);

The reason for using CAST versus SUBDATE is CAST is ANSI SQL syntax. SUBDATE is a MySQL specific implementation of the date arithmetic component of CAST. Getting into the habit of using ANSI syntax can reduce headaches should you ever have to migrate to a different database. It's also good to be in the habit as a professional practice as you'll almost certainly work with other DBMS' in the future.

None of the major DBMS systems are fully ANSI compliant, but most of them implement the broad set of ANSI syntax whereas nearly none of them outside of MySQL and its descendants (MariaDB, Percona, etc) will implement MySQL-specific syntax.

---

I adapted one of the above answers from cdhowie as I could not get it to work. This seems to work for me. I suspect it's also possible to do this with the UNIX_TIMESTAMP function been used.

SELECT * FROM your_table

WHERE UNIX_TIMESTAMP(DateVisited) >= UNIX_TIMESTAMP(CAST(NOW() - INTERVAL 1 DAY AS DATE))
  AND UNIX_TIMESTAMP(DateVisited) <= UNIX_TIMESTAMP(CAST(NOW() AS DATE));