CODEWITHSUNDEEP
mysqlsql
allen
allen

Reputation: 380

SQL query for interval of time

I have two tables - user and orders. In user is all information about users in orders is orders with dt_star and dt_end - for services. When they buy a service i write current date in dt_start and after 3 months the service is expired. I must send email 5 days before the service is expired on everyone user. I will do this with Cronjob, but the problem is to select 5 days before expired service for everyone user. My query is something like this:

`SELECT `user`.* FROM `user` 
LEFT JOIN `orders` ON orders.user_id=user.id 
WHERE CURDATE() > '`orders`.`dt_end`' - interval 5 day 
LIMIT 30`

but like this is not working ...

Upvotes: 1

Views: 867

Answers (3)

Dmitry Surin
Dmitry Surin

Reputation: 351

In OracleDB it will be kinda like this:

SELECT * FROM user, orders
WHERE orders.user_id = user.id
AND sysdate BETWEEN orders.dt_end - 5 AND orders.dt_end;

Upvotes: 2

CrazyProgrammer
CrazyProgrammer

Reputation: 544

using left join is not correct here. also typecast the output as date

`SELECT `user`.* FROM `user`, `orders` 
   where orders.user_id=user.id 
    and CURDATE() = date('`orders`.`dt_end`' - interval 5 day) 
LIMIT 30`

this will give list of all users should be notified 5 days before

Upvotes: 1

flyingfox
flyingfox

Reputation: 13506

Change to 

SELECT `user`.* FROM `user` 
  LEFT JOIN `orders` ON orders.user_id=user.id 
   WHERE CURDATE() > DATE_SUB( `orders`.`dt_end` ,interval 5 day )
LIMIT 30

Upvotes: 3

Related Questions