CODEWITHSUNDEEP
mysql
Sayantan Das
Sayantan Das

Reputation: 1641

mysql BETWEEN date range not working

I have a table called barcode_log, and these are all the datas from the table.

All results

And now if I run this query 

SELECT * FROM `barcode_log` WHERE barcode_log.assign_time BETWEEN DATE_SUB(CURRENT_DATE, INTERVAL 30 DAY) AND CURRENT_DATE;

I get this result

query result

But it should return all the rows as all the data is within this month only. And assign_time field is stored as datetime. Any idea what i am doing wrong??

Upvotes: 2

Views: 1503

Answers (3)

Constantine Kurbatov
Constantine Kurbatov

Reputation: 895

There is another way around: CAST() on barcode_log.assign_time field.

SELECT * 
FROM `barcode_log` 
WHERE CAST(barcode_log.assign_time AS DATE)
BETWEEN DATE_SUB(CURRENT_DATE, INTERVAL 30 DAY) AND CURRENT_DATE;

This excludes time from comparison and works fine for your purpose.

Upvotes: 0

Bart Friederichs
Bart Friederichs

Reputation: 33511

While the accepted answer works, there is a simpler solution. Just take the date part of the datetime column:

SELECT 
     * 
FROM 
    `barcode_log` 
WHERE 
    DATE(barcode_log.assign_time) 
    BETWEEN DATE_SUB(CURRENT_DATE, INTERVAL 30 DAY) AND CURRENT_DATE;

Upvotes: 2

1000111
1000111

Reputation: 13519

You are ignoring the time part (hh:mm:ss).

If the end day is set to the end timestamp of the current date then you can get the data of current day's too.

BETWEEN is inclusive 

SELECT
    *
FROM
    `barcode_log`
WHERE
    barcode_log.assign_time BETWEEN DATE_SUB(
        CURRENT_DATE,
        INTERVAL 30 DAY
    )
AND TIMESTAMP(CONCAT(CURDATE(),' ','23:59:59'));

Upvotes: 2

Related Questions