MySQL selecting yesterday’s date

by

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.

More Related Contents:

  1. i have an error which i dont understand. can you help me? [closed]
  2. MySQL select 10 random rows from 600K rows fast
  3. How do you use the “WITH” clause in MySQL?
  4. How to change max_allowed_packet size
  5. Simulate lag function in MySQL
  6. mysql error 1364 Field doesn’t have a default values
  7. SQL: What is the default Order By of queries?
  8. MySQL Split Comma Separated String Into Temp Table
  9. Can I use a function for a default value in MySql?
  10. Access Denied for User ‘root’@’localhost’ (using password: YES) – No Privileges?
  11. Get record counts for all tables in MySQL database
  12. Query to get all rows from previous month
  13. How do I check if a column is empty or null in MySQL?
  14. Use MySQL spatial extensions to select points inside circle
  15. MySQL versus PDO [duplicate]
  16. Mysql string split [duplicate]
  17. Increment a database field by 1
  18. Measuring actual MySQL query time
  19. Am I Using JDBC Connection Pooling?
  20. MySQL VARCHAR Lengths and UTF-8
  21. mysql transaction – roll back on any exception
  22. Find and replace entire MySQL database
  23. How to find non-existing data from another Table by JOIN?
  24. is there a mysql function to decode html entities?
  25. Unable to AES_DECRYPT after AES_ENCRYPT in mysql
  26. How to Prevent Connection Timeouts for Large MySQL Imports
  27. Strings as Primary Keys in MYSQL Database [closed]
  28. What is the default MySQL JOIN behaviour, INNER or OUTER?
  29. How to get the mysql table columns data type?
  30. mySQL regex in the where clause

Leave a Comment