CODEWITHSUNDEEP
mysql
aalaap
aalaap

Reputation: 4401

How to get rows which have a date that's either today or was yesterday or the day before in MySQL?

My MySQL database table has dates stored in the MySQL format (YYYY-MM-DD). I need to construct an SQL query that will return rows with dates that either occur today or yesterday or a day before. For example, if the stored date is 1979-08-22, I would like this row returned if today is 8/22 (the same date in a different year) or 8/23 or 8/24.

I'm thinking of using the DAYOFYEAR() function, which returns a single value between 1 and 365 depending on the date. Here is the current query:

SELECT * FROM `theTable` WHERE DAYOFYEAR(CURDATE())-DAYOFYEAR(`theDate`) IN(0, 1, 2, -364, -363)

This query finds the difference in days between the current date and the stored date and if it's 3 days, it should return the result. It seems to work.

Is this the right way to achieve the result I want or is there another, more efficient way? Is this likely to cause issues with leap years?

Upvotes: 1

Views: 475

Answers (5)

aalaap
aalaap

Reputation: 4401

SELECT * FROM `datesTable`
    WHERE (
        MONTH(`theDate`) = MONTH(CURDATE())
        AND DAY(`theDate`) = DAY(CURDATE())
    ) OR (
        MONTH(`theDate`) = MONTH(DATE_ADD(CURDATE(), INTERVAL -1 DAY))
        AND DAY(`theDate`) = DAY(DATE_ADD(CURDATE(), INTERVAL -1 DAY))
    ) OR (
        MONTH(`theDate`) = MONTH(DATE_ADD(CURDATE(), INTERVAL -2 DAY))
        AND DAY(`theDate`) = DAY(DATE_ADD(CURDATE(), INTERVAL -2 DAY))
    )

Done!

Upvotes: 0

shesek
shesek

Reputation: 4682

Does it specifically have to be today, yesterday, or the day before - or the last 72 hours is also okay?

SELECT * FROM foo WHERE UNIX_TIMESTAMP(field) BETWEEB CURRENT_TIMESTAMP - 259200 AND CURRENT_TIMESTAMP

update It seems like I completly missed the part about the years being different... another possible solution (which works on days diff and not hours) which I added as a comment is WHERE DATEDIFF(DATE_FORMAT(CURDATE(), '1990-%m-%d'), DATE_FORMAT(field, '1990-%m-%d')) <= 2

Upvotes: 1

preinheimer
preinheimer

Reputation: 3722

This seems harder than it needs to be. What's wrong with something like:

WHERE thedate >= DATE_SUB(CURRENT_DATE(), INTERVAL 2 DAY)

If future dates are possible you will require an additional clause: AND thedate <= CURRENT_DATE()

Treating dates like strings and trying to do math is wrought of peril. Leap years, leap seconds, edge cases over year ends, etc. all cause problems. If there's a built in library capable of solving your problem you're much better off using it, rather than rolling your own.

Upvotes: 1

nulll
nulll

Reputation: 1603

If you use mysql function on where clause, the optimizer can't use the index on that field (if there is an index on it)

You can check how the optimizer is going to treat your query with the EXPLAIN command

EXPLAIN SELECT * FROM `theTable` WHERE DAYOFYEAR(CURDATE())-DAYOFYEAR(`theDate`) IN(0, 1, 2, -364, -363)

If the table is small and will not grow and your not concerned about performance you should not worry.

Me I would add a new VARCHAR field beside the DATETIME field and I would store the same information in this field but in a different order

DATETIME: 2011-01-01
VARCHAR: 01-01-2011

And I would create an index on the varchar field, this way you could run fast and easy extraction query like

SELECT * FROM `theTable` WHERE `varchar_date` LIKE '01-01-%'

Upvotes: 1

Nicola Cossu
Nicola Cossu

Reputation: 56357

select *,
if(right(date_field,5)<=right(curdate(),5),concat(year(curdate()),'-',right(date_field,5)),concat(year(curdate() - interval 1 year),'-',right(date_field,5))) as myfield
from table
having myfield between curdate() - interval 2 day and curdate()

Upvotes: 1

Related Questions