Date calculations in the query

Asked

Viewed 92 times

2

I’ve been having a hard time for some time. I’ve been researching, but I haven’t found anything that would help me.

I need to make a calculation in a query, is the following. I need to take the day the record was entered into the bank and then do an operation with the current date, I have to subtract the current date by the date that was entered into the bank, if the value of the subtraction is greater than 3 , I will know that this record is late. So I need to do this to register my table, and count how many are late. I know I should use COUNT(*) , DATADIFF, INTERVAL, and so on, but I couldn’t. Is it possible ? How would it be ? Thank you.

mysql date

  • What is the structure of your table?

  • Basically, I have the code (int), Varchar client name, Data_operation (TIMESTAMP)

  • You will use DATEDIFF to help solve the problem. I advise you to visit this site: https://www.w3schools.com/sql/func_mysql_datediff.asp Just follow the tutorial and you’ll be happy. I hope I’ve helped!

2 answers

2

Follow an example:

SELECT curdate() hoje, Data_operacao
, DATEDIFF(curdate(), Data_operacao) as dias
, (
case 
when DATEDIFF(curdate(), Data_operacao) <= 3 then 0
else (DATEDIFF(curdate(), Data_operacao) - 3)
end 
) dias_atraso
FROM NOME_SUA_TABELA

Total delay is just add the total return of the table.

SELECT 
 sum(
case 
when DATEDIFF(curdate(), Data_operacao) <= 3 then 0
else (DATEDIFF(curdate(), Data_operacao) - 3)
end 
) as TOTAL_atraso
FROM NOME_SUA_TABELA

I hope I’ve helped.

  • 1

    First of all thank you for your help. But wouldn’t you be able to create a Count to give me the amount? I am worried about the performace, because my table is giant. rs

  • 1

    I updated the reply with a query with the total delay

  • 1

    Very good guy... Only that has a problem , she is returning me a wrong amount, I have 2 lines late in the bank , at the end he is returning me 4, why ?

  • 1

    For that you would need to know your situation in more detail.... can you ride a Fiddle? http://sqlfiddle.com/

  • 1

    I thank you too much. Only unfortunately will not give time now , because I have to leave. But tomorrow very early I ride and show you. Thanks anyway.

2


SELECT DATEDIFF(data_operacao, NOW()) AS Diferenca_Dias FROM Tabela

Hence in the language you are using you do the check. If the result you gave is greater than 3 is late.

  • First of all thank you for your help. But wouldn’t you be able to create a Count to give me the amount? I am worried about the performace, because my table is giant. rs

Browser other questions tagged mysql date

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