How to get dates with X days interval?

Asked

Viewed 127 times

2

I have a field data in a table. And every day I will search:

SELECT * FROM table WHERE DATE_ADD( data, INTERVAL 7 DAY ) = CURDATE( )

That is, if the date of the table + 7 days is equal to today. This to get the records from 7 days ago.

But in addition to those records from 7 days ago I also want the records from 7+7 days ago, 7+7+7 days ago, etc.

For example, if today is day 22-11-2019(KURDATE) I want to fetch all records with dates 15-11-2019, 08-11-2019, 01-11-2019 and so on every seven days.

Example in DB Fiddle.

mysql sql date
  • 1

    If you can get the difference of days between data and CURDATE (maybe DATEDIFF?), it is sufficient that this difference is multiple of 7. If you want the dates strictly past, the difference needs to be negative (or data < CURDATE()); if you want all dates except the current one, it must be different from 0

  • @Jeffersonquesado can make an answer?

  • Something like (DAY(CURDATE()) - DAY(data)) % 7 = 0 or something more optimized than that

  • 2

    @Woss, rather than convert into absolute days and subtract is just pick up the difference

  • And if the Kurds return 21-11-2019, what would be the expected return?

  • @Guilhermenascimento all records of 14-11-2019, 07-11-2019, 31-10-2019, etc decreasing every 7 days.

  • A last doubt, it will always be from CURDATE (what is specified in the question), or can it possibly be filtered by smaller dates? Assuming that the set date (Kurdish or not) will be the maximum range.

  • Yes it will always be from the KURDISH back.

Show 3 more comments

3 answers

6


Check if the difference between dates is a multiple of 7:

select * from tabela where MOD(DATEDIFF(CURDATE(), data), 7) = 0;

This select will even bring a record with data = CURDATE(), so if you don’t want that add and data != CURDATE(). Or even, if you want strictly dates prior to the current addition and data < CURDATE() and, of course, you won’t even need and data != CURDATE().

See working here

  • Combined with the same day verification probably will be necessary to check whether data is prior to the current date, not to return the 2019-11-21 date, even though today is 2019-11-14 and the check is the same month/year, not to return the 2019-10-31 date, for example. solution remains the same, only preciousness to achieve, in fact, the desired in the question.

  • @Woss All right, I’ll edit

  • A good answer, it still seems to me that the question contains some details, I believe that the search waits the return of the retroactive only, and not of the current date, but this because of what the AP reported: if today is day 22-11-2019 I want to go get all the records with dates 15-11-2019, 08-11-2019, 01-11-2019, is nothing complicated, it would be one more condition that the AP itself resolves applying select *&#xA;from tabela&#xA;where date < CURDATE() AND MOD(DATEDIFF(CURDATE(), date), 7) = 0&#xA;ORDER by date DESC; ...

  • ... which can even solve another situation, the search for different dates of KURDISH... I think maybe this is it: https://www.db-fiddle.com/f/usf57EbBmteTvPiPG1EGHb/2. (cc @Jorgeb. confirms whether this is it for me or not, please)

  • 1

    @Guilherme Nascimento date < CURDATE() was already in my answer

  • @Guilhermenascimento is that, sir.

  • 1

    @I missed F5 here, I didn’t see Edit :) +1 for the answer.

Show 2 more comments

1

You could use the following SELECT:

SELECT * FROM table WHERE MOD(DATEDIFF(CURDATE(), data), 7) = 0

Let’s go to the explanation:

The function DATEDIFF() returns the interval in days between the current date CURDATE() and the date entered in your database data.

Already the function MOD() returns the rest of a division, so when the division is exact this number is multiple of the parameter passed for comparison, in case 7, so MOD( break in days, 7 ).

At the end, you should make a simple comparison to check if the result returned by the function MOD() is equal to zero (0), indicating that the division of days by seven is exact.

0

A solution will be to load all the data to a cursor, and use the interval value (7) in a variable and increment with 7 in each iteration and so fetch all the intervals of the database, until finished.

NOTE: If you share your example in Sqlfiddle, I can help you with the cursor.

SET @interval = -7;
SELECT * FROM table 
WHERE `data` >= DATE(NOW()) + INTERVAL @interval DAY;

  • I added a fiddle to the question

  • 1

    I found it strange to use cursor for such purpose, and I particularly cannot see the cursor converging to any result (perhaps because I am ignorant with the use of cursors). Could you further develop the answer? , even more so now that the AP has put a fiddle to base?

Browser other questions tagged mysql sql date

You are not signed in. Login or sign up in order to post.