MySQL selecting yesterday s date

Question

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 many 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

User · Answer

SELECT SUBDATE NOW   1     where now   function returs current date and time of system in Timestamp     you can use   SELECT SUBDATE CURDATE   1

User · Answer

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

User · Answer

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   gt   UNIX TIMESTAMP CAST NOW   - INTERVAL 1 DAY AS DATE     AND UNIX TIMESTAMP DateVisited   lt   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  gt   CAST NOW   - INTERVAL 1 DAY AS DATE    AND DateVisited  lt   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

User · Answer

Last or next date  week  month  amp  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

User · Answer

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

User · Answer

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  gt   UNIX TIMESTAMP CAST NOW   - INTERVAL 1 DAY AS DATE     AND DateVisited  lt   UNIX TIMESTAMP CAST NOW   AS DATE

User · Answer

Query for the last weeks   SELECT   FROM dual WHERE search date BETWEEN SUBDATE CURDATE    7  AND CURDATE

User · Answer

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   gt   UNIX TIMESTAMP CAST NOW   - INTERVAL 1 DAY AS DATE     AND UNIX TIMESTAMP DateVisited   lt   UNIX TIMESTAMP CAST NOW   AS DATE

[mysql] MySQL selecting yesterday's date

Examples related to mysql