CODEWITHSUNDEEP
mysqlsqlmariadb
Lunatic Fnatic
Lunatic Fnatic

Reputation: 681

Date Intervals Doesn't work

I am trying to get data by week, month and year.

I store date YYYY-MM-DD HH:MM:SS.

What I am doing is below;

Fetch one week old data;

query + AND WEEK(date) = WEEK(CURDATE())

Fetch a month old data;

query + AND MONTH(date) = MONTH(CURDATE())

The thing is I couldnt be able to get the data correct. For instance when I want to get week old data, I am gettin a year old one too.

Is there any other query that I could use? I have tried DATE(NOW()) - INTERVAL 30 DAY. It works but very slow.

Thanks!

Upvotes: 0

Views: 174

Answers (2)

Rick James
Rick James

Reputation: 142306

WHERE ...
  AND date >= CURDATE() - INTERVAL 7 DAY
  AND date  < CURDATE()

Gives you the 7 days ending with yesterday. Use other techniques to get a particular month or week.

This technique is also much faster for large tables with a suitable index. Hiding date inside a function, such as WEEK() prevents the use of an index.

Upvotes: 0

Tom H
Tom H

Reputation: 47464

I believe that the problem is that the WEEK function returns the week of the year. So, Jan 1st 2017 might be week 1 (also might be week 53 of the previous year depending on the day of the week and how MySQL handles it). But then, Jan 1st of 2016 is also week 1 - just for a different year.

Trying changing it to:

query + AND WEEK(date) = WEEK(CURDATE()) AND YEAR(date) = YEAR(CURDATE())

Also, if you're storing this as a string then definitely change it to a DATETIME

Upvotes: 1

Related Questions