How to compare Timestamp in where clause
57,312
Solution 1
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
Solution 2
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'
Author by
Basel
Updated on July 05, 2022Comments
-
Basel almost 2 years
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??