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??
When comparing two TIMESTAMP WITH TIME ZONE values, the comparison is made using the UTC representations of the values. Two TIMESTAMP WITH TIME ZONE values are considered equal if they represent the same instance in UTC, regardless of the time zone offsets that are stored in the values. For example, '1999-04-15-08.00.
Can you compare timestamps in SQL? A date, time, or timestamp value can be compared with another value of the same data type, a datetime constant of the same data type, or with a string representation of a value of that data type.
Can you compare datetime in SQL? The right way to compare date only values with a DateTime column is by using <= and > condition. This will ensure that you will get rows where date starts from midnight and ends before midnight e.g. dates starting with '00:00:00.000' and ends at "59:59:59.999".
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
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'
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With