mysql BETWEEN date range not working

Question

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

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

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

Is BETWEEN inclusive? Because this might work tomorrow. You are comparing dates, not times. — Bart Friederichs
– Bart Friederichs, Commented Oct 5, 2016 at 8:00
Perhaps taking just the date of barcode_log.assign_time might work: WHERE DATE(barcode_log.assign_time) BETWEEN ... — Bart Friederichs
– Bart Friederichs, Commented Oct 5, 2016 at 8:01

1000111 · Accepted Answer · 2016-10-05 08:07:28Z

2

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'));

answered Oct 5, 2016 at 8:07

1000111

13.5k2 gold badges32 silver badges43 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Sayantan Das Over a year ago

Thank you. This worked. Didn't know about BETWEEN being inclusive.

1000111 Over a year ago

SELECT 2 BETWEEN 2 AND 3; - This simplest example shows between's inclusive behavior very well.

Bart Friederichs · Accepted Answer · 2016-10-05 08:31:26Z

2

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;

answered Oct 5, 2016 at 8:31

Bart Friederichs

33.8k16 gold badges112 silver badges219 bronze badges

1 Comment

1000111 Over a year ago

Nice way to handle this through the date part. But one potential problem is index on assign_time field cannot be used (if any).

Constantine Kurbatov · Accepted Answer · 2021-04-19 20:40:18Z

0

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.

answered Apr 19, 2021 at 20:40

Constantine Kurbatov

93515 silver badges16 bronze badges

Collectives™ on Stack Overflow

mysql BETWEEN date range not working

3 Answers 3

2 Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related