Calcular intervalos de períodos de tempo com MySQL

Atualmente trabalho no MX Cursos, e foi implementado uma aplicação de assinaturas. E em sistemas de assinatura, você tem planos que definem um período de tempo dessa assinatura, e  a partir dai tem as recorrências, de período e período seriam solicitados as cobranças, pra retornar a assinatura e assim continuar tendo acesso.

Essas cobranças, na maioria das vezes, é o próprio Gateway de pagamento realiza, mandando para o cliente, e aplicação recebe a notificação que foi realizado o pagamento ou cancelamento da assinatura, entre outros status.

Precisávamos saber uma estimativa de valor que seria necessário receber nesse mês. Em planos mensais é mais fácil, mas nos outros planos, você precisaria respeita os intervalos dos planos, como trimestral, semestral, anual, para ter uma estimativa mais exata do quanto deveria entrar naquele mês.

Para melhor visualizar, vou criar uma imagem de uma tabela que seria de assinatura

tabela2

Ela é composta de uma chave primaria, id do plano, o id do cliente, o valor da assinatura, status da assinatura, a data que foi criado assinatura, e uma data de expiração da assinatura.

Uma lógica que poderíamos aplicar seria o seguinte:

Pegar a diferença em meses data de hoje com a data da criação da tabela, e depois utilizar operador módulo para calcular como valor do intervalo, e se o valor for igual a zero, sabemos que aquela assinatura iria renovar naquele mês.

(DATA ATUAL – DATA INICIAL) % INTERVALO == ZERO

Esse cálculo você poderia utilizar a linguagem mesmo para calcular em cada loop da assinatura, fazer essa diferença e a partir dai, dizer se essa assinatura será recobrado naquele mês.

Porém, podemos utilizar apenas o SQL.

Como falei, precisamos ter um intervalo em meses da diferença entre as duas data,  para isso podemos utilizar PERIOD_DIFF.

Sendo que o PERIOD_DIFF você passa no formato YYMM ou YYYYMM

Podemos usar EXTRACT para extrair parte uma data, e usando o parâmetro YEAR_MONTH para retornar o ano e o mês.

E para calcular o módulo, podemos usar o MOD

Vamos a SQL completa, no qual vamos usar o intervalo de 3 meses (trimestral) que ficaria assim:

SELECT * FROM subscription WHERE MOD(PERIOD_DIFF(EXTRACT(YEAR_MONTH FROM NOW()), EXTRACT(YEAR_MONTH FROM created)), 3) = 0

Se fosse semestral, alteraria 3 para 6, anual para 12, e assim sucessivamente.

Se você tiver outra forma de realizar essa tafera, adicione nos comentários, com isso todos nós vamos aprendendo.

2 thoughts on “Calcular intervalos de períodos de tempo com MySQL

  1. Nesse caso, o SQL ta verificando se ja se passaram 3 meses desde a data de criação da assinatura do cliente em questão, certo? … Tudo bem.
    Mas não deveriam ser levados em conta os dias desde a data de criação? Ai só se leva em consideração o ano e o mês.

    Os dias são importantes pro caso de uma re-assinatura. Pois é preciso somar quantos dias restam com os dias da nova assinatura pra o próximo vencimento.

    Ótimo tutorial!

    1. A ideia aqui é mais relacionado em meses, para relatório. Se fosse pra dias, poderia utilizar o campo expired, q seria data da expiração da assinatura. E quando é renovada, é alterado a expired novamente, é mais facil fazer assim.

Deixe uma resposta

O seu endereço de e-mail não será publicado. Campos obrigatórios são marcados com *