Welcome to WebmasterWorld Guest from 54.196.208.187

Forum Moderators: open

Message Too Old, No Replies

get rows with 3 day interval between two date fields

     
11:51 am on Jan 23, 2015 (gmt 0)

Senior Member from ES 

WebmasterWorld Senior Member 10+ Year Member Top Contributors Of The Month

joined:July 24, 2002
posts: 1129
votes: 2


hi,

we sell event tickets and i am having a bit of a slow moment here.

i'd like to get all orders which were made within 3 days of the event.

currently i have:

select sum(quantity) from orders where 
and date_purchase >= DATE(event_date - INTERVAL 3 DAY);


this returns a plausible result (lol), but i've never been good with date interval and wondered if someone could confirm?

many thanks
3:31 pm on Jan 23, 2015 (gmt 0)

Senior Member

WebmasterWorld Senior Member 10+ Year Member

joined:Dec 15, 2003
posts:2623
votes: 4


Looks good, that will get any orders made in the 3 days leading up to the event but it will catch any orders made after the event, not sure if that can happen but...if you want to be extra OCD... You can add in an condition that checks if the order wasn't "after" the event date by adding:

select sum(quantity) from orders where
(date_purchase >= DATE(event_date - INTERVAL 3 DAY) and date_purchase <= event_date);

Adding that ensures it was purchased within 3 days of the event or the event date itself, but not after.
5:14 pm on Jan 23, 2015 (gmt 0)

Senior Member from ES 

WebmasterWorld Senior Member 10+ Year Member Top Contributors Of The Month

joined:July 24, 2002
posts: 1129
votes: 2


perfect demaestro! much obliged
5:32 pm on Jan 23, 2015 (gmt 0)

Moderator from US 

WebmasterWorld Administrator lifeinasia is a WebmasterWorld Top Contributor of All Time 10+ Year Member Top Contributors Of The Month

joined:Dec 10, 2005
posts:5811
votes: 157


Are date_purchase and event_date strictly date fields or do they have time components as well? If you have time components, the "date_purchase <= event_date" part may not work without additional tweaking.
5:53 pm on Jan 23, 2015 (gmt 0)

Senior Member from ES 

WebmasterWorld Senior Member 10+ Year Member Top Contributors Of The Month

joined:July 24, 2002
posts: 1129
votes: 2


the date_purchase does have a time element; so i'd have to do

select sum(quantity) from orders where
(DATE_FORMAT(date_purchase, '%Y-%m-%d') >= DATE(event_date - INTERVAL 3 DAY) and DATE_FORMAT(date_purchase, '%Y-%m-%d') <= event_date);

does that look correct? i've just tested that and it works well. comparing it to the query without the DATE_FORMAT there is a 10% discrepancy in results. with this more exact result returning 10% more records.