MySQL选择昨天的日期 [英] MySQL selecting yesterday's date
问题描述
如何显示和计算日期为昨天的值?
我使用time()
在数据库中插入日期.示例:
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
我想显示表中存在多少个具有多个URL的URL,以及昨天已经访问了多少个URL.结果示例:
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
我已经有了获取昨天日期的想法,但是我没有一个想法来计算昨天存在一个URL的次数并计算表中存在URL的次数.
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.
推荐答案
获取昨天日期的最简单,最好的方法是:
The simplest and best way to get yesterday's date is:
subdate(current_date, 1)
您的查询将是:
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
出于好奇,sum(condition)
为您提供满足条件的行的case
语句)是因为在mysql布尔值中1
为true,0
为false,因此对条件求和有效地算出其为真的次数.使用此模式可以整理您的SQL代码.
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.
这篇关于MySQL选择昨天的日期的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!