For example, there is a table type of meetings, it has a numeric field "time for which an event can be overdue," in minutes. There is a table of events where there is a period of events. How to select events that have expired by more than "time _..."

SELECT * FROM crmTasks WHERE ( date(crmTaskDueDate) < date_add(curdate(), INTERVAL - **crmTaskTypeNormaTimeMin** MIN) )

This design does not work ...

  • And just curdate() - INTERVAL ... ? And try to make queries so as not to apply the function to the table column, i.e. what would be just crmTaskDueDate, and not date () from it. You force the optimizer to do a full table scan using the optimizer function, even if you have an index on the date column - Mike
  • Thanks, I'll keep it in mind. There is a sense that different types of meetings have different times, so I can’t write "curdate - 10 minute", for example - Nina Voros
  • Do you need it, I understand it is rounded by date? those. if you went 10 minutes in the previous day - then you need to take immediately from the beginning of those days? For some reason, it seems to me that if the countdown is in minutes, then it is necessary to take time into account, for example, use now () instead of curdate () - Mike
  • ABOUT! No, I do not need rounding by date, now I’ll go read manuals for date () apparently this is the error !! - Nina Voros
  • Then yes, use now () - interval X minute. curdate () gives a bare date. now () gives the current date and time - Mike

3 answers 3

 UPDATE crmTasks INNER JOIN crmTaskTypes ON crmTasks.crmTasksTypeId = crmTaskTypes.crmTaskTypeId SET crmTasksStateId=9, crmTasksResultId=2 ,crmTaskDueDate=date_add(curdate(),INTERVAL - 1 HOUR) WHERE (TIMESTAMP( crmTaskDueDate ) < TIMESTAMP( NOW( ) ) - crmTaskTypeNormaTimeMin *60)

Here is the final decision. Thank you @Mike and @cheops (reputation is not enough to mark your answers)

    Alternatively, translate the time into seconds and subtract the value of crmTaskTypeNormaTimeMin multiplied by 60 from them. Next, compare these values.

     SELECT * FROM crmTasks WHERE TIMESTAMP(date(crmTaskDueDate) < TIMESTAMP(curdate()) - crmTaskTypeNormaTimeMin * 60
    • I understand that getting into my head / base / query is difficult, but I’m doing something wrong .... TIMESTAMP (date (crmTaskDueDate) shows 2016-08-08 09:05:00 a (TIMESTAMP (curdate () ) - crmTaskTypeNormaTimeMin * 60) shows 20160807999100 and obviously doesn’t want to compare, or rather not what it doesn’t want, does not produce lines that logically should - Nina Voros
     SELECT * FROM crmTasks WHERE crmTaskDueDate < now() - INTERVAL **crmTaskTypeNormaTimeMin** MINUTE