MySQL selecting yesterday's date

Solution 1:

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.

Solution 2:

SELECT SUBDATE(NOW(),1);

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

you can use:

SELECT SUBDATE(CURDATE(),1)

Solution 3:

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

Solution 4:

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)