MySQL 如何比较where子句中的时间戳
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/18917871/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to compare Timestamp in where clause
提问by Basel
I have a table with timestamp column i want to get the values where the timestamp in specific month (for example where the timpestamp between 1 september and 30 septemper) taking in considration if the month is 31 day. I use this query:
我有一个带有时间戳列的表，我想获取特定月份的时间戳（例如 9 月 1 日和 9 月 30 日之间的时间戳）的值，如果月份是 31 天。我使用这个查询：
SELECT users.username, users.id, count(tahminler.tahmin)as tahmins_no FROM users LEFT JOIN tahminler ON users.id = tahminler.user_id GROUP BY users.id having count(tahminler.tahmin) > 0
Can i add where timestamp IN(dates_array)??
可以加where timestamp IN(dates_array)吗？？
date_array will be the dates of the whole month??
date_array 将是整个月的日期？？
回答by juergen d
SELECT users.username, users.id, count(tahminler.tahmin)as tahmins_no FROM users LEFT JOIN tahminler ON users.id = tahminler.user_id where year(timestamp) = 2013 and month(timestamp) = 9 GROUP BY users.id having count(tahminler.tahmin) > 0
To make it work with indexes you can do
要使其与索引一起使用，您可以执行以下操作
SELECT users.username, users.id, count(tahminler.tahmin)as tahmins_no
FROM users
LEFT JOIN tahminler ON users.id = tahminler.user_id
where timestamp >= '2013-09-01' and timestamp < '2013-10-01'
GROUP BY users.id
having count(tahminler.tahmin) > 0
回答by SriniV
In case of TIMESTAMP
在 TIMESTAMP 的情况下
YEAR (TIMESTAMP) = 2013 AND MONTH (TIMESTAMP) = 9
To include in the same clause
包含在同一条款中
DATE_FORMAT(TIMESTAMP,'%Y-%m')='2013-09'
For unix time stamp
对于unix时间戳
YEAR (FROM_UNIXTIME(TIMESTAMP)) = 2013 AND MONTH(FROM_UNIXTIME(TIMESTAMP))=9
To include in the same clause
包含在同一条款中
DATE_FORMAT(FROM_UNIXTIME(TIMESTAMP),'%Y-%m')='2013-09'
回答by Dan Bellandi
If I understand your question correctly you can just add:
如果我正确理解您的问题，您可以添加：
WHERE MONTH(timestamp)=9
