Calcular tempo decorrido entre duas datas

clear glass with red sand grainerComo calcular a idade de uma pessoa em anos, meses e dias e utilizando T-SQL? Ao pesquisar na web encontram-se vários métodos “mágicos” mas que falham em algum momento.

E quantos dias úteis se passaram desde que aquela fatura venceu?

Ou ainda quantas horas úteis foram dedicadas pelo colaborador a um projeto, ao longo do mês?

1. Introdução

Em vários aplicativos é necessário calcular qual o tempo que decorreu entre duas datas, seja em dias corridos ou úteis, seja em horas. O resultado geralmente é em dias (ou horas), mas às vezes é necessário que o resultado seja detalhado em dias, horas, minutos e segundos. A princípio pode-se pensar em utilizar a função DATEDIFF() mas como a granularidade solicitada pode variar é necessário ficar atento a não misturar as granularidades, como visto no item “Função DATEDIFF() capciosa” do artigo “Dominando datas e horas no SQL Server“.

Antes de prosseguir, uma observação. Desenvolver códigos T-SQL exige tempo e dedicação para estudos e testes, afora conhecimento técnico. Sempre que utilizo código T-SQL de outra pessoa como base eu informo o nome do autor e/ou o endereço web da página onde foi publicado, de modo a respeitar o esforço de programação de quem o desenvolveu. Do contrário tem-se o plágio, que, além de ser uma violação dos direitos autorais, é uma demonstração de falta de ética profissional. Nesse ponto faço minhas as palavras de Edvaldo Castro em seu artigo “PLÁGIO – Sério mesmo?”.

2. Criação das tabelas FERIADO e CALENDÁRIO

O primeiro passo é construir a tabela de feriados e, opcionalmente, a tabela com o calendário contendo informação sobre todos os dias.

2.1. Tabela FERIADO

A estrutura da tabela de feriados depende do contexto do banco de dados. Por exemplo, ele pode ser de uma empresa que está instalada em um único município e que somente é necessário conhecer quando é feriado no município, além dos feriados federais e estaduais e conforme as regras da atividade principal da empresa. Nesse caso podemos ter algo como

-- código #1
-- Autor: José Diz
-- Publicado em: Porto SQL - https://portosql.wordpress.com

CREATE TABLE dbo.FERIADO (
     Data date not null,
     Abrangencia char(1) not null 
     check (Abrangencia in ('M', 'E', 'N')),
     constraint PK_FERIADO primary key (Data)
);

Para cada dia que seja feriado somente pode existir uma única linha, com a indicação da abrangência do feriado: municipal (M), estadual (E) ou nacional (N). Se em uma mesma data há mais de um feriado, com abrangências diferentes, deve-se cadastrar o de mais alta abrangência.

É uma tabela simples mas que contém pouca informação. A primeira informação que falta é sobre o motivo do feriado. Outra informação que pode ser útil é se é um feriado religioso ou oficial, além de registrar se é um feriado fixo ou móvel.

Podemos então ampliar a tabela de feriados para a seguinte estrutura:

-- código #2
-- Autor: José Diz
-- Publicado em: Porto SQL - https://portosql.wordpress.com

CREATE TABLE dbo.FERIADO (
     Data date not null,
     Abrangencia char(1) not null 
         check (Abrangencia in ('M', 'E', 'N')), 
     Origem char(1) not null 
        check (Origem in ('R','C')), -- religioso ou civil
     Tipo char(1) not null 
         check (Tipo in ('F', 'M')), -- fixo ou móvel
     Descricao varchar(80),
     constraint PK_FERIADO primary key (Data, Abrangencia)
);

Pelo fato de ter sido acrescentada a descrição do feriado, a chave primária da tabela passou a ser composta, pois em uma mesma data podem existir um feriado nacional e um municipal, por exemplo, o que significa que existirão duas linhas com a mesma data na tabela.

Observe que, para fins didáticos, a tabela possui uma estrutura simples. Entretanto, dependendo da atividade da empresa, se ela possui instalações em localidades diversas e em diferentes estados, o registro de feriados é mais complexo podendo inclusive envolver mais de uma tabela.

Para fins de desenvolvimento deste artigo o código T-SQL da carga inicial da tabela com os feriados nacionais de 2020 é o seguinte:

-- código #3
-- Autor: José Diz
-- Publicado em: Porto SQL - https://portosql.wordpress.com

set dateformat dmy;

-- feriados nacionais de 2020
INSERT into dbo.FERIADO 
     (Data, Abrangencia, Origem, Tipo, Descricao)
  values 
     ('1/1/2020', 'N', 'C', 'F', 'Confraternização universal'),
     ('10/4/2020', 'N', 'R', 'M', 'Paixão de Cristo'),
     ('21/4/2020', 'N', 'C', 'F', 'Tiradentes'),
     ('1/5/2020', 'N', 'C', 'F', 'Dia mundial do Trabalho'),
     ('7/9/2020', 'N', 'C', 'F', 'Independência do Brasil'),
     ('12/10/2020', 'N', 'R', 'F', 'Nossa Senhora Aparecida'),
     ('2/11/2020', 'N', 'C', 'F', 'Finados'),
     ('15/11/2020', 'N', 'C', 'F', 'Proclamação da República'),
     ('25/12/2020', 'N', 'R', 'F', 'Natal');

2.2. Tabela CALENDÁRIO

A tabela com o calendário desenvolvida neste artigo também considera somente uma localidade, de modo a facilitar a demonstração das soluções. O sábado não foi considerado como dia útil.

-- código #4
-- Autor: José Diz
-- Publicado em: Porto SQL - https://portosql.wordpress.com

CREATE TABLE dbo.CALENDARIO (
     Data date not null,
     Dia as cast (datepart (day, Data) as tinyint) persisted,
     Mes as cast (datepart (month, Data) as tinyint) persisted,
     Ano as cast (datepart (year, Data) as smallint) persisted,
     DiaSemana varchar(20) not null,
     SemanaAno tinyint not null,
     DiaUtil bit not null,
     Feriado bit not null,
     constraint PK_CALENDARIO primary key (Ano, Mes, Dia)
);

CREATE unique nonclustered INDEX I2_CALEND_Data 
       on dbo.CALENDARIO (Data);

A carga da tabela CALENDARIO pode ser feita utilizando o seguinte código T-SQL:

-- código #5
-- Autor: José Diz
-- Publicado em: Porto SQL - https://portosql.wordpress.com

declare @DataInicial date, @DataFinal date;
set @DataInicial= convert (date, '1/1/2020', 103);
set @DataFinal= convert (date, '31/12/2020', 103);

--
declare @Dias integer;
set @Dias= datediff (day, @DataInicial, @DataFinal) +1;

set datefirst 7; -- semana começa no domingo
set language brazilian; -- para datename retornar em português
set nocount on;

with
Tally (n) as (
SELECT row_number() over (order by (SELECT NULL))
  from (values (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) as A(n)
       cross join (values (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) as B(n)
       cross join (values (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) as C(n)
),
Periodo as (
SELECT dateadd (day, (T.N -1), @DataInicial) as Dia
  from Tally as T
  where T.N <= @Dias
)
INSERT into dbo.CALENDARIO (Data, DiaSemana, SemanaAno, DiaUtil, Feriado)
  SELECT P.Dia, datename (weekday, P.Dia), datepart (week, P.Dia),
         case when datepart (weekday, P.Dia) in (1, 7)
                   or exists (SELECT * from dbo.FERIADO as F where F.Data = P.Dia)
              then 0 else 1 end,
         case when exists (SELECT * from dbo.FERIADO as F where F.Data = P.Dia)
              then 1 else 0 end
  from Periodo as P;

Para gerar as datas foi utilizada tally table, com capacidade de até 1.000 dias. Se for necessário gerar período maior, basta alterar a tally table.

3. Tempo decorrido entre duas datas

Neste capítulo estão algumas formas de calcular o tempo decorrido entre duas datas, podendo ser dias corridos ou úteis ou ainda em horas úteis.

3.1. Tempo decorrido em dias corridos

Para calcular o tempo decorrido em dias corridos entre duas datas, com o resultado retornado em dias, horas, minutos e segundos, eis uma solução:

-- código #6
-- Autor: José Diz
-- Publicado em: Porto SQL - https://portosql.wordpress.com

declare @D1 datetime2, @D2 datetime2;
set @D1= datetime2fromparts (2011, 10, 15, 18, 20, 35, 0, 0);
set @D2= datetime2fromparts (2011, 11, 13, 12, 30, 55, 0, 0);

SELECT format (@D1, 'dd/MM/yyyy HH:mm:ss') as D1,
       format (@D2, 'dd/MM/yyyy HH:mm:ss') as D2,
       (datediff_big (second, @D1, @D2) / 86400) as tDia,
       (datediff_big (second, @D1, @D2) / 3600 % 24) as tHor,
       (datediff_big (second, @D1, @D2) / 60 % 60) as tMin,
       (datediff_big (second, @D1, @D2) % 60) as tSeg;

cujo resultado é

p034_codigo #6

O cálculo inicial usando a função DATEDIFF() é a diferença entre as duas datas: 2.484.620 segundos. A seguir temos:

  1. um dia tem 86.400 segundos; ao dividir 2.484.620 por 86.400 obtemos 28 como quociente, que é o número de dias;
  2. uma hora tem 3.600 segundos; ao dividir 2.484.620 por 3.600 obtemos 690 horas como quociente; dividimos então por 24 (número de horas, por dia) e o resto é o número de horas além do valor calculado em (1);
  3. um minuto tem 60 segundos; ao dividir 2.484.620 por 60 obtemos 41.410 minutos; dividimos então por 60 e o resto é o número de minutos além do valor calculado em (2).

Às vezes é solicitado que se forneça por extenso o tempo decorrido entre as datas, sendo que a solução proposta no código #6 pode ser facilmente adaptada para tal.

Substitua DATEDIFF_BIG() por DATEDIFF() caso a versão do SQL Server seja anterior a 2016.

O código #6 pode ser transformado facilmente em função de usuário do tipo inline table-valued, conforme código T-SQL a seguir:

-- código #11
-- Autor: José Diz
-- Publicado em: Porto SQL - https://portosql.wordpress.com

CREATE or alter FUNCTION dbo.intervalo_DHMS (
     @D1 datetime2, @D2 datetime2
)
returns table
return
SELECT cast ((datediff_big (second, @D1, @D2) / 86400) as int) as tDia,
       cast ((datediff_big (second, @D1, @D2) / 3600 % 24) as tinyint) as tHor,
       cast ((datediff_big (second, @D1, @D2) / 60 % 60) as tinyint) as tMin,
       cast ((datediff_big (second, @D1, @D2) % 60) as tinyint) as tSeg;
go

E se for necessário tratar também meses e anos? Como o número de dias por mês é variável, não é possível utilizar a mesma abordagem do código anterior. A solução requer alguns cálculos adicionais e no artigo How to Compute Date and Time Difference in Parts, de Itzik Ben-Gan, é apresentada uma solução para esses casos.

3.2. Tempo decorrido em dias úteis

O algoritmo para calcular o tempo decorrido em dias úteis entre duas datas depende da existência ou não da tabela CALENDARIO. Se existir, é algo bem simples:

-- código #7
-- Autor: José Diz
-- Publicado em: Porto SQL - https://portosql.wordpress.com

declare @D1 date, @D2 date;
set @D1= datefromparts (2020, 4, 9);
set @D2= datefromparts (2020, 4, 22);

SELECT convert (char(10), @D1, 103) as [Data inicial],
       convert (char(10), @D2, 103) as [Data final],
       count(*) as [Dias úteis]
  from dbo.CALENDARIO as C
  where C.Data between @D1 and @D2
        and C.DiaUtil = 1;

cujo resultado é

p034_codigo #7

Moleza!

O período escolhido foi proposital, pois nele há 2 feriados.

O código #7 pode ser transformado facilmente em função de usuário do tipo inline table-valued, conforme código T-SQL a seguir:

-- código #12
-- Autor: José Diz
-- Publicado em: Porto SQL - https://portosql.wordpress.com

CREATE or alter FUNCTION dbo.intervalo_D (
     @D1 date, @D2 date
)
returns table
return
SELECT count(*) as diasUteis
  from dbo.CALENDARIO as C
  where C.Data between @D1 and @D2
        and C.DiaUtil = 1;
go

Se não existir a tabela CALENDARIO, a função datepart (weekday) e a tabela FERIADO podem ser utilizadas para calcular o tempo decorrido em dias úteis entre duas datas, pulando dias úteis e feriados.

-- código #8
declare @D1 date, @D2 date;
set @D1= datefromparts (2020, 4, 9);
set @D2= datefromparts (2020, 4, 22);

set datefirst 7; -- semana inicia no domingo (padrão de DATEDIFF)
SELECT convert (char(10), @D1, 103) as [Data inicial],
       convert (char(10), @D2, 103) as [Data final],
       (datediff (day, @D1, @D2) + 1)
        - (datediff (week, @D1, @D2) * 2)
        - (case when datepart (weekday, @D1) = 1 then 1 else 0 end)
        - (case when datepart (weekday, @D2) = 7 then 1 else 0 end)
        - (SELECT count(*) from dbo.FERIADO
           where Data between @D1 and @D2
                 and datepart (weekday, Data) not in (1, 7)) as [Dias úteis];

O código T-SQL anterior foi baseado no artigo Calculating Work Days, de Jeff Moden, sendo acrescentada a tabela de feriados.

3.3. Tempo decorrido em horas úteis

O algoritmo para calcular o tempo decorrido em horas (e minutos) úteis entre duas datas depende da existência ou não da tabela CALENDARIO. Além disso, é necessário ter a informação de qual é o horário de serviço, em turnos ou corrido (sem intervalo).

Para desenvolvimento inicial vamos considerar que é necessário calcular quantas horas úteis determinado colaborador dedicou a uma atividade. O colaborador trabalha 30 horas semanais, de segunda-feira a sexta-feira e em turno único de 6 horas, das 13h às 19h.

-- código #9
-- Autor: José Diz
-- Publicado em: Porto SQL - https://portosql.wordpress.com

declare @D1 datetime2(0), @D2 datetime2(0);
set @D1= datetime2fromparts (2020, 4, 9, 15, 55, 0, 0, 0);
set @D2= datetime2fromparts (2020, 4, 22, 15, 54, 0, 0, 0);

declare @Horario_inicio time(0), @Horario_fim time(0), 
        @Minutos_dia int;
set @Horario_inicio= '13:00';
set @Horario_fim= '19:00';
set @Minutos_dia= datediff (minute, @Horario_inicio, @Horario_fim);

with
calcDiasUteis as (
SELECT (@Minutos_dia * count(*)) as MinDias
  from dbo.CALENDARIO as C
  where C.Data > cast (@D1 as date)
        and C.Data < cast (@D2 as date)
        and C.DiaUtil = 1
),
calcMinutosUteis as (
SELECT (MinDias
       + case when cast (@D1 as date) = cast (@D2 as date)
                 then datediff (minute, @D1, @D2)
              when @D2 > @D1
                 then datediff (minute, cast (@D1 as time(0)), @Horario_fim)
                      + datediff (minute, @Horario_inicio, cast (@D2 as time(0)))
               else 0 end) as MinutosUteis
  from calcDiasUteis
)
SELECT format (@D1, 'dd/MM/yyyy HH:mm:ss') as D1,
       format (@D2, 'dd/MM/yyyy HH:mm:ss') as D2,
       MinutosUteis,
       (MinutosUteis / @Minutos_dia) as Dia,
       (MinutosUteis % @Minutos_dia /60) as Hora,
       (MinutosUteis % 60) as Minuto
  from calcMinutosUteis;

cujo resultado é

p034_codigo #9

A primeira CTE, calcDiasUteis, calcula quantos dias úteis há entre o dia seguinte ao início e o dia anterior ao final, multiplicando esse valor por 360, que é o número de minutos úteis de um dia de trabalho do colaborador.

A segunda CTE, calcMinutosUteis, calcula a quantidade de minutos que o colaborador trabalhou na data inicial e na data final e os soma ao valor calculado na CTE anterior. cabe destacar que esta CTE considera que as datas informadas para início e término são dias úteis.

Ao final, a quantidade de minutos calculada é exibida como minutos totais e também como dias, horas e minutos.

O código T-SQL anterior pode ser ampliado para outras condições, como mais de um turno ao dia, por exemplo. Também pode ser parametrizado e transformado em procedimento armazenado ou mesmo em função de usuário do tipo inline table-valued.

3.4. Calcular idade

Calcular idade de uma pessoa nada mais é do que calcular o tempo decorrido entre duas datas. No item “Tempo decorrido em dias corridos” foram propostas soluções para cálculos de dias, horas, minutos e segundos mas no caso de cálculo de idade é necessário retornar em anos, meses e dias.

Uma das soluções comuns para o cálculo de anos é programar algo como:

SELECT datediff (year, Data_nasc, @Hoje) ...

Entretanto, é necessário ter cuidado com a função DATEDIFF() pois ela considera somente a diferença entre os valores de anos sem verificar se o mês e dia já ocorreram no ano corrente. Considerando-se as seguintes datas de nascimento e que o dia de hoje seja 19 de outubro de 2018, teríamos os seguintes valores

p034_quadro1

Quem nasceu em 20 de outubro de 1968 ainda não teria 50 anos em 19 de outubro de 2018 mas sim 49 anos, faltando um dia para completar os 50 anos. Perceberam o erro no cálculo? Ou seja, para calcular a idade correta é necessário verificar se a pessoa já aniversariou ou não no ano, através da análise das partes de dia e mês da data de nascimento e data corrente:

SELECT case when month(@Hoje) > month(Data_nasc)
               then datediff (year, Data_nasc, @Hoje)
            when month(@Hoje) = month(Data_nasc)
                 and day(@Hoje) >= day(Data_nasc)
               then datediff (year, Data_nasc, @Hoje)
            else (datediff (year, Data_nasc, @Hoje) -1)
       end as Anos

O algoritmo acima subtrai 1 do cálculo de anos se o aniversário ainda não ocorreu no ano. O mesmo procedimento é necessário para calcular o número de meses, sendo que para calcular o número de dias o procedimento é ligeiramente diferente.

Falta, é claro, o cálculo de meses e dias. Utilizando a lógica acima, uma forma de calcular a idade em anos, meses e dias pode ser a seguinte:

-- código #10
-- Autor: José Diz
-- Publicado em: Porto SQL - https://portosql.wordpress.com

USE NorthWind;

declare @Hoje date, @Dia int, @Mes int;
set @Hoje= cast (sysdatetime() as date);
set @Dia= datepart (day, @Hoje);
set @Mes= datepart (month, @Hoje);

SELECT concat (FirstName, ' ', LastName) as Nome,
       convert (char(10), BirthDate, 103) as [Data nascimento],
       case when @Mes > month (BirthDate)
                 then datediff (year, BirthDate, @Hoje)
            when @Mes = month( BirthDate) and @Dia >= day (BirthDate)
                 then datediff (year, BirthDate, @Hoje)
            else (datediff (year, BirthDate, @Hoje) -1)
       end as Anos,
       case when @Dia >= day(BirthDate)
                 then (datediff (month, BirthDate, @Hoje) % 12)
            else ((datediff (month, BirthDate, @Hoje) -1) % 12)
       end as Meses,
       case when @Dia >= day (BirthDate) 
                 then (@Dia - day (BirthDate))
            else (@Dia + day (eomonth (BirthDate)) - day (BirthDate))
       end as Dias 
  from dbo.Employees;

cujo resultado é

p034_codigo #10

O código #10 pode ser transformado facilmente em função de usuário do tipo inline table-valued, conforme código T-SQL a seguir:

-- código #14
-- Autor: José Diz
-- Publicado em: Porto SQL - https://portosql.wordpress.com

CREATE or alter FUNCTION dbo.intervalo_AMD (
     @D1 date, @D2 date
)
returns table
return
with Partes as (
SELECT Dia_D1= day (@D1), Mes_D1= month (@D1),
       Dia_D2= day (@D2), Mes_D2= month (@D2)
)
SELECT case when Mes_D2 > Mes_D1
                 then datediff (year, @D1, @D2)
            when Mes_D2 = Mes_D1 and Dia_D2 >= Dia_D1
                 then datediff (year, @D1, @D2)
            else (datediff (year, @D1, @D2) -1)
       end as Anos,
       case when Dia_D2 >= Dia_D1
                 then (datediff (month, @D1, @D2) % 12)
            else ((datediff (month, @D1, @D2) -1) % 12)
       end as Meses,
       case when Dia_D2 >= Dia_D1 
                 then (Dia_D2 - Dia_D1)
            else (Dia_D2 + day (eomonth (@D1)) - Dia_D1)
       end as Dias
  from Partes;
go

Como é uma função que retorna uma tabela, ela pode ser acionada através de CROSS APPLY:

-- código #15 
-- Autor: José Diz
-- Publicado em: Porto SQL - https://portosql.wordpress.com 

declare @Hoje date;
set @Hoje= cast (sysdatetime() as date);

SELECT concat (E.FirstName, ' ', E.LastName) as Nome,
       convert (char(10), E.BirthDate, 103) as [Data nascimento],
       D.Anos, D.Meses, D.Dias
  from Northwind.dbo.Employees as E
       cross apply dbo.intervalo_AMD (cast (E.BirthDate as date), @Hoje) as D;

 


Este artigo contém trechos do capítulo 4 do artigo “Dominando datas e horas no SQL Server“, que é um guia sobre como utilizar datas e horas em T-SQL, contendo mais de 60 páginas em tamanho A4.

Deixe um comentário

Preencha os seus dados abaixo ou clique em um ícone para log in:

Logotipo do WordPress.com

Você está comentando utilizando sua conta WordPress.com. Sair /  Alterar )

Foto do Google

Você está comentando utilizando sua conta Google. Sair /  Alterar )

Imagem do Twitter

Você está comentando utilizando sua conta Twitter. Sair /  Alterar )

Foto do Facebook

Você está comentando utilizando sua conta Facebook. Sair /  Alterar )

Conectando a %s