CODEWITHSUNDEEP
mysqlsql
Arefe
Arefe

Reputation: 12397

The MySQL query return an empty set

I have a MySQL table named product from where I would like to retrieve top 3 data within a date range in decreasing order by quantity. My table is provided, 

# product_id, stock_id, quantity, stock_timestamp
Product ID9, Stock ID2, 1050, 2019-03-04 07:06:37
Product ID8, Stock ID2, 950, 2019-03-04 07:06:27
Product ID7, Stock ID2, 85000, 2019-03-04 07:06:17
Product ID6, Stock ID1, 750, 2019-03-04 07:06:07
Product ID5, Stock ID1, 65000, 2019-03-04 07:05:57
Product ID4, Stock ID1, 550, 2019-03-04 07:05:47
Product ID3, Stock ID, 450, 2019-03-04 07:05:37
Product ID27, Stock ID5, 1950, 2019-02-28 07:05:17
Product ID26, Stock ID5, 1850, 2019-02-28 07:05:17
Product ID25, Stock ID5, 1750, 2019-02-28 07:05:17
Product ID24, Stock ID4, 211650, 2019-02-24 07:05:17
Product ID23, Stock ID4, 1550, 2019-02-24 07:05:17
Product ID22, Stock ID4, 1450, 2019-02-24 07:05:17

I write the SQL query as provided, 

SELECT * FROM StockHandler.product WHERE stock_timestamp BETWEEN  DATE("2019-03-03") AND DATE("2019-03-04")  ORDER BY quantity DESC  LIMIT 3 ;

I get an empty value from the returned query. I find no error in the SQL. So, how do I write it correctly?

Upvotes: 2

Views: 215

Answers (3)

TonyCao
TonyCao

Reputation: 1

MySql will treat

DATE("2019-03-03") AND DATE("2019-03-04")

as

DATE("2019-03-03 00:00:00") AND DATE("2019-03-04 00:00:00")

You can use @fa06's answer or edit the time to 

DATE("2019-03-03") AND DATE_ADD('2019-03-04',INTERVAL 1 DAY)

Upvotes: 0

Hasitha Amarathunga
Hasitha Amarathunga

Reputation: 2005

Try without date()

SELECT * FROM StockHandler.product 
WHERE date(stock_timestamp) BETWEEN  '2019-03-03' AND '2019-03-04' 
ORDER BY quantity DESC  LIMIT 3

Upvotes: 1

Fahmi
Fahmi

Reputation: 37473

Try below - date() for stock_timestamp

SELECT * FROM StockHandler.product 
WHERE date(stock_timestamp) BETWEEN  '2019-03-03' AND '2019-03-04' 
ORDER BY quantity DESC  LIMIT 3

Upvotes: 2

Related Questions