如何在where子句中比较时间戳 [英] How to compare Timestamp in where clause
本文介绍了如何在where子句中比较时间戳的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我有一个带有timestamp列的表,我想获取特定月份(例如9月1日至9月30日之间的timpestamp)在考虑月份(如果该月份为31天)时需要考虑的值. 我使用以下查询:
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:
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
我可以添加where timestamp IN(dates_array)
??
date_array将是整个月的日期?
date_array will be the dates of the whole month??
推荐答案
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
这篇关于如何在where子句中比较时间戳的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文