CODEWITHSUNDEEP
mysqlsqldateselect
Sam
Sam

Reputation: 1828

SQL Date interval not working with AND clause

I am trying to count views in time interval. The request below works only without the AND clause

If I take out the AND s.timestamp < 2019-01-31 it works just fine.

SELECT s.category_id, c.name, count(s.category_id) AS ViewCount 
FROM stat_product_category s 
JOIN category c ON s.category_id = c.id 
WHERE s.timestamp > 2019-01-01 AND s.timestamp < 2019-01-31
GROUP BY s.category_id 
ORDER By ViewCount Desc 
LIMIT 10

Upvotes: 1

Views: 571

Answers (3)

Simon
Simon

Reputation: 537

that script is looking good you just need to pop qoutes around your dates so that SQL knows where the values start & stops.

SELECT s.category_id, c.name, count(s.category_id) AS ViewCount 
FROM stat_product_category s 
JOIN category c ON s.category_id = c.id 
WHERE s.timestamp > '2019-01-01' AND s.timestamp < '2019-01-31'
GROUP BY s.category_id 
ORDER By ViewCount Desc 
LIMIT 10

(tested & working!)

Good luck with the project!

Upvotes: 0

Mureinik
Mureinik

Reputation: 311528

Those aren't timestamps - without quotes, you just have a couple of ints you're subtracting. 2019-01-01 is evaluated as 2019-1-1, or 2017. 2019-01-31 is evaluated as 2019-1-31, or 1987. There is no number that's greater than 2017 but smaller than 1987, so you get no results. Surrounding these values with quotes will make them a string literal, and allow the database to perform an implicit conversion to a date:

SELECT s.category_id, c.name, count(s.category_id) AS ViewCount 
FROM stat_product_category s 
JOIN category c ON s.category_id = c.id 
WHERE s.timestamp > '2019-01-01' AND s.timestamp < '2019-01-31'
-- Here ------------^----------^-------------------^----------^
GROUP BY s.category_id 
ORDER By ViewCount Desc 
LIMIT 10

Upvotes: 2

Zaynul Abadin Tuhin
Zaynul Abadin Tuhin

Reputation: 31993

you need single quote on date value

SELECT s.category_id, c.name, count(s.category_id) AS ViewCount 
FROM stat_product_category s 
JOIN category c ON s.category_id = c.id 
WHERE s.timestamp > '2019-01-01' AND s.timestamp <'2019-01-31'
GROUP BY s.category_id ,c.name
ORDER By ViewCount Desc 
LIMIT 10

Upvotes: 2

Related Questions