There is such a request:

SELECT * FROM `payments` WHERE `MEMBER_ID`= 1052

it is necessary to exclude records on the field with the date, if the date is more than a year ago. tried this:

 AND (NOW() - PAYMENT_DATE) < 31536000

However, this condition does not work, nothing is displayed, although there are dates for 2016

  • PAYMENT_DATE <(NOW () - 31536000) - Artem Gorlachev
  • Date in what format, calendar? - cheops
  • yes, but when I tried to do SELECT *, NOW () - PAYMENT_DATE was displayed in the timestamp - Jonny Manowar
  • @Artem Gorlachev doesn't work either, and it seems like there is no difference - Jonny Manowar

4 answers 4

 SELECT * FROM `payments` WHERE `MEMBER_ID`= 1052 AND PAYMENT_DATE > now() - interval 1 year

Try to write any comparisons so that the table columns are not enclosed in any functions or mathematical operations. Try to rearrange the arguments of the expression so that the blank field is compared with some calculated (without using data from the tables) value. Because in this case, firstly, the value itself will be calculated only once, and not for each line again. Second, if there is a suitable index, the optimizer will be able to use it.

    Can try this way

     SELECT * FROM `payments` WHERE `MEMBER_ID`= 1052 AND (TO_DAYS(NOW()) - TO_DAYS(PAYMENT_DATE) <= 365) 
       AND DATEFIFF(NOW(), PAYMENT_DATE) < 365

      So it works exactly in mysql if the column is of datetime type.

        Understood:

          (UNIX_TIMESTAMP(NOW()) - UNIX_TIMESTAMP(PAYMENT_DATE)) < 31536000

        NOW () must be cast to the timestamp.

        • My variant is still to try, in theory it should work and it is clearly more optimal for the reasons described in the answer - Mike
        • yes, apparently he will work faster - Jonny Manowar