Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How to compare Timestamp in where clause

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??

like image 515
Basel Avatar asked Sep 20 '13 13:09

Basel


People also ask

How do you compare a timestamp?

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?

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.

How do I compare only the date part of a timestamp in SQL?

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".


2 Answers

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
like image 119
juergen d Avatar answered Oct 27 '22 20:10

juergen d


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'
like image 42
SriniV Avatar answered Oct 27 '22 21:10

SriniV