How to compare Timestamp in where clause

Question

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

Can i add where timestamp IN(dates_array)??

date_array will be the dates of the whole month??

You should use `BETWEEN` clause, if you want to check an interval. Check out here: http://dev.mysql.com/doc/refman/5.0/en/comparison-operators.html#operator_between — Minoru, Sep 20 '13 at 13:29

juergen d · Accepted Answer · 2013-09-20T14:18:26.923

19

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

edited Sep 20 '13 at 14:18

answered Sep 20 '13 at 13:21

juergen d

201,996
37
293
362

+1 It's not actually clear that it is supposed to be 2013 only but that seems most likely. – Dan Bellandi Sep 20 '13 at 13:27
It gives me a Error: Token Mismatch – Basel Sep 20 '13 at 13:50
@BaselShbeb: That does not sound like a MySQL error message. Maybe your error is somewhere else. – juergen d Sep 20 '13 at 13:58
@BaselShbeb: I added an extra info. – juergen d Sep 20 '13 at 14:19

score 4 · Answer 2 · answered Sep 20 '13 at 13:32

In case of 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

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'

score 0 · Answer 3 · answered Sep 20 '13 at 13:24

0

If I understand your question correctly you can just add:

WHERE MONTH(timestamp)=9

answered Sep 20 '13 at 13:24

Dan Bellandi

526
3
5

it gives me Error: token mismatch – Basel Sep 20 '13 at 13:50
Do you have it before the `GROUP BY` clause? – Dan Bellandi Sep 20 '13 at 13:52

How to compare Timestamp in where clause

3 Answers3

Linked