Selecting Date + 3 Days

[MoFish]

Hi,

 

Another date query which I'm struggling with. Would anyone be able to help me at all?

 

I have the following table.

ID, DDATE

1, 09/02/2017 (date/time)
2, 15/02/2017 (date/time)
3, 20/02/2017 (date/time)

I'm trying to only select the values from the DB where the CURRENTDATE is equal to the DDATE value + 3 days afterwards.

 

For example: Once the CURRENDATE is 09/02/2017 it should return row 1 until the CURRENTDATE hits the 12/02/2017 as that's + 3 days.

 

It should not return row 2 or 3 until the actual date (15/02/2017) or (20/02/2017) is the CURRENTDATE

 

I tried this myself a few times and failed - the results are not correct. I used UTC_DATE() in my queries as my server is based in America and i'm in UK.

 

Any help much appreciated.

 

Thanks,

 

MoFish

SELECT * FROM `TABLE` WHERE `DDATE` = UTC_DATE() AND `DDATE` BETWEEN (DATE((`DDATE`) + INTERVAL 3 DAY) AND UTC_DATE())

SELECT * FROM `TABLE` WHERE (`DDATE`) BETWEEN UTC_DATE() AND (`DDATE`) + INTERVAL 3 DAY

SELECT * FROM TABLE WHERE UTC_DATE() = DATE(`DDATE`) + 3 DAY INTERVAL

[requinix]

So would it be accurate to describe that as wanting the latest date you have available that is no more than 3 days in the future?

[MoFish]

Hi requinex,

 

I think in short terms yes.

 

select where the current date matches the ddate where it is no more than 3 in the future.

[requinix]

If you think about it that way, it's straightforward: find all the rows that aren't too far into the future, sort them by the date in descending order (latest first), and pick the first one.

SELECT whatever FROM table WHERE date is no more than 3 days in the future ORDER BY date DESC LIMIT 1

[MoFish]

Hi Requinix, Thanks for your reply.

 

I may have not communicated correctly what I am trying to achieve - as there could often be more than one result.

 

Let's say I have the following data in my TABLE and today's date is the 09/02/2017

ID, DDATE

1, 09/02/2017 (date/time)
2, 15/02/2017 (date/time)
3, 20/02/2017 (date/time)
4, 07/02/2017 (date/time)
5, 09/02/2017 (date/time)
6, 06/02/2017 (date/time)

I would expect this to return the following results:

ID, DDATE

1, 09/02/2017 (today)
4, 07/02/2017 (tomorrow - inside 3 day range)
5, 09/02/2017 (today)
6, 06/02/2017 (3rd day ago - inside 3 day range)

I thought the SQL query would have been something like the following - however this does not work nor is it fully valid.
 

// SELECT * FROM TABLE WHERE DATE IS BETWEEN TODAYS DATE AND TODAYS DATE + 3 DAYS

SELECT * FROM TABLE WHERE (`DDATE` BETWEEN UTC_DATE() AND UTC_DATE() + 3 DAY INTERVAL);

Regards,

 

MoFish

Edited February 9, 2017 by MoFish

[requinix]

Did you look at the query I wrote? I don't think you did, because if you had then you would know it only returns one result.

And if it wasn't clear, there is a bit in the middle you need to do by yourself.

[MoFish]

Hi Requinex,

 

Yes, I looked at your query - however noticed you had put a limit on the end of the query - which made me think we were talking about differing things. I could potentially expect more than one result as per my example results above. I have tried multiple times to do the 'middle bit' but am struggling a little. I thought my query above was close.

Edited February 9, 2017 by MoFish

[Jacques1]

For some reason, you keep switching between the right syntax and the wrong syntax. It's

INTERVAL 3 DAY

Teh manual

[MoFish]

Jacques1 & requinex - Sorry about this.

 

I'm still not getting the results I require. The following is still not returning the correct data - even without the -.

 

I have attempted so many queries myself and none appear to be correct.

SELECT * FROM TABLE WHERE DATE(`DDATE`) = DATE_ADD(UTC_DATE(), INTERVAL 3 DAY)

[requinix]

Oh, you don't want just the one result? You want all of them?

SELECT * FROM table WHERE ddate I'd really have expected you to try that by now, though.

[MoFish]

Hi requinex,

 

Yes, I want all of the results. I have tried your query - however this is not working quite as expected. Maybe this will help explain what I'm trying to do:

if TODAY is in the 3 DAY RANGE of DDATE value - then display results

Edited February 9, 2017 by MoFish

[Barand]

Do you mean

SELECT * 
FROM table 
WHERE ddate BETWEEN UTC_DATE() - INTERVAL 3 DAY AND UTC_DATE() + INTERVAL 3 DAY

[MoFish]

Hi Barand,

 

Thanks for this. This was not producing the results as I was expecting - however I have modified the query and this seems to be working now.

SELECT * FROM TABLE WHERE `DDATE` BETWEEN (UTC_DATE() - INTERVAL 2 DAY) AND UTC_DATE()

Thanks everyone for taking the time to respond.

 

MoFish

[requinix]

Your solution does not match your description, which is probably why no one was able to get you the answer you wanted, but at least you figured it out eventually.