[SOLVED] How to fix syntax error in query interval?

Issue

select *, 
   (select max(h.date) 
    from hour_cards as h  
    where h.subordinate_id = hour_cards.subordinate_id
      and h.date > hour_cards.date and 
   h.date - hour_cards.date <= INTERVAL 1 minute) as wrong_entry
   from hour_cards

I’m trying to make a query that will give me all entries of each worker if he/she read card twice or more when entering or exiting work, but the above query gives me errors all time.

#1064 – You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘) as wrong_entry
from hour_cards

Solution

You can use interval only in date calculation function.

Your query must look like:

select *, 
   (select max(h.date) 
    from hour_cards as h  
    where h.subordinate_id = hour_cards.subordinate_id
      and h.date > hour_cards.date and 
   DATE_ADD(h.date, INTERVAL 1 minute) <= hour_cards.date  as wrong_entry
   from hour_cards

Answered By – Jens

Answer Checked By – Gilberto Lyons (BugsFixing Admin)