'How to fix syntax error in query interval?

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

mysql


Solution 1:[1]

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

Solution 2:[2]

You can use TIMESTAMPDIFF to make it correct :

 TIMESTAMPDIFF(MINUTE,h.date,hour_cards.date) <= 1

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1
Solution 2 Sagar Joon

Related Questions