Where to delete records that were entered a certain time ago

Asked

Viewed 55 times

-1

Help to create a delete with where if the record has been inserted for more than 5 minutes.

I have the point marking table with the columns:

  • cd_register - integer
  • cd_enterprise - whole
  • cd_matricula - integer
  • dt_dot - varchar (because I am sending from the application Java as String the date)

The idea is:

delete from ponto where "registro foi inserido há mais de cinco minutos"

or

delete from ponto where hora_atual - dt_ponto > cincoMinutos

Or if there is another alternative to this solution.

I need this because it’s a company rule that a person can only enter point record every five minutes. So this is a table that serves as a point marking time reference. In the java application, I do a check, if there is registration in this table is because the user registered point less than five minutes and can not enter another record.

mysql sql database oracle

  • Tells an example of what you want to do, it is easy to solve this, but sends the sample data

  • You should not check for duplicates, and if there is more than one record in less than 5 minutes, do the removal?

  • no, because it is necessary to avoid that there is more than one insertion by the same person in less than five minutes.

  • Convert your string to a datetime field and use the function DATE_SUB with INTERVAL 5 MINUTES.

  • @Guilhermebrügger, thank you very much for the tip, really. And thank you all for collaborating on the solution!

1 answer

1


Use the function DATE_SUB() to calculate the difference of 5 minutes date, thus:

WHERE dt_ponto <= DATE_SUB(NOW(), INTERVAL 5 MINUTE);

The INTERVAL can be HOUR, MINUTE, etc, and as is DATE_SUB, will subtract 5 minutes, if it were DATE_ADD, sum.

See here an example working: https://www.db-fiddle.com/f/mrJ1axymtQmoHLhKJb2bXc/0

  • in his case, he has the dt_ponto as varchar function also?

  • yes, just convert to datetime

Browser other questions tagged mysql sql database oracle

You are not signed in. Login or sign up in order to post.