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.
ベストアンサー1
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.