Jump to content
MoFish

Selecting Date + 3 Days

Recommended Posts

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

Share this post


Link to post
Share on other sites

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?

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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 by MoFish

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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 by MoFish

Share this post


Link to post
Share on other sites

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)

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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 by MoFish

Share this post


Link to post
Share on other sites

Do you mean

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

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.


×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.