MySQL selecting yesterday's date

Question

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

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);

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));