Agregando horas no SQL

Usamos sempre funções para agregação de valores e nunca nos damos conta como elas são importantes no dia-a-dia. Quem já tentou agregar horas no SQL para obter um retorno de horas maior que 23:59 deve ter percebido que não é tão simples quanto parece, mais existem técnicas que foram listada por Jeff Smith no site do Sqlteam e estarei demostrando aqui como utiliza-las.

Imagine que te solicitaram um relatório que devera informar a quantidade total de horas que cada veiculo da empresa ficou em manutenção. Vamos ambientar nossos testes nesta variável do tipo table com os seguintes dados:

SET DATEFORMAT DMY

DECLARE @ManutencaoVeiculos TABLE
(
    Id INT IDENTITY(40,1),
    ModeloVeiculo VARCHAR(50),
    DataEntradaManutencao DATETIME,
    DataSaidaManutencao DATETIME
)

INSERT INTO @ManutencaoVeiculos VALUES
    ('VeiculoAAA','01/09/2012 15:03', '02/09/2012 09:00')
INSERT INTO @ManutencaoVeiculos VALUES
    ('VeiculoBBB','03/09/2012 11:21', '03/09/2012 12:00')
INSERT INTO @ManutencaoVeiculos VALUES
    ('VeiculoCCC','01/09/2012 08:01', '02/09/2012 09:05')
INSERT INTO @ManutencaoVeiculos VALUES
    ('VeiculoDDD','10/09/2012 18:48', '21/09/2012 09:00')
INSERT INTO @ManutencaoVeiculos VALUES
    ('VeiculoAAA','01/09/2012 15:03', NULL)
INSERT INTO @ManutencaoVeiculos VALUES
    ('VeiculoDDD','23/09/2012 10:20', '29/09/2012 08:50')

Obtendo o seguinte resultado:

Nesta tabela existe uma coluna com a data em que o veiculo entrou em manutenção e outra coluna com a data de saída do veiculo de manutenção preenchida, caso não esteja preenchida o veiculo ainda se encontra em manutenção. A técnica consiste em obter o total de segundos entre as datas de manutenção do veiculo para posteriormente estar convertendo no padrão horas:minutos:segundos.

Para auxiliar na obtenção do total de segundos entre as datas utilizaremos da função do SQL DATEDIFF, esta função retorna um inteiro de acordo com os três parâmetros devidamente preenchidos, sendo o primeiro denominado datepart, que consiste em informamos o que queremos obter, devemos passar SECOND ou as suas abreviações (SS, S) para obtemos o total de segundos entre as datas. O segundo parâmetro é startdate onde a coluna “DataEntradaManutencao” se encaixa perfeitamente e o terceiro e ultimo parâmetro é o enddate onde utilizaremos a coluna “DataSaidaManutencao”, veja código abaixo:

SELECT
    *
FROM
(
    SELECT
        Id,
        ModeloVeiculo ,
        DataEntradaManutencao,
        DataSaidaManutencao,
        ISNULL(DATEDIFF(SECOND,DataEntradaManutencao,DataSaidaManutencao),0) TotalDeSegundos
    FROM
        @ManutencaoVeiculos
)
AS
    Aggr

Com o total de segundos utilizaremos de um técnica que consiste em transformarmos os totais de segundos entre as datas nas informações que desejamos, no caso o tempo total de manutenção de cada veiculo.
Para obtermos o total de horas de manutenção de cada veiculo, basta dividir o total de segundo por 3600, pois sabemos que 1 hora é igual 60 segundos * 60 minutos. Os minutos usam de uma formula parecida, porem usamos do operador MOD (%) no SQL para auxiliar na obtenção do resto da visão por 3600, logo em seguida com o resultado desta operação dividimos por 60.

O operador MOD consiste em obter o resto de uma divisão, logo se dividirmos 3 por 2 o restante é 1, o que seria a soma dos valores após a virgula (3 / 2 = 1,5, logo 0,5 + 0,5 = 1). Os segundos é apenas o restante da divisão por 60, recapitulado:

  • Horas       = (Total de segundos / 3600)
  • Minutos    = ((Total de segundos % 3600) / 60)
  • Segundos = (Total de segundos % 60)

Com as formulas acima conseguiremos converter o total de segundos em total de horas/minutos/segundos (HH:MM:SS) que cada veiculo ficou em manutenção, veja query abaixo e seu respectivo resultado:

SELECT
    Aggr.Id,
    Aggr.ModeloVeiculo,
    Aggr.TotalDeSegundos,
    CAST(Aggr.TotalDeSegundos / 3600 AS VARCHAR) + ':' +
    CAST((Aggr.TotalDeSegundos % 3600) / 60 AS VARCHAR) + ':' +
    CAST((Aggr.TotalDeSegundos % 60) AS VARCHAR) AS 'HH:MM:SS'
FROM
(
    SELECT
        Id,
        ModeloVeiculo,
        ISNULL(DATEDIFF(SECOND,DataEntradaManutencao,DataSaidaManutencao),0) TotalDeSegundos
    FROM
        @ManutencaoVeiculos
)
AS
    Aggr

O menos difícil é agregar o total de horas, basta modificarmos a query para ela agrupar pelo ModeloVeiculo e somar o total de segundos que esta sendo obtido pela função Datediff, veja:

SELECT
    Aggr.ModeloVeiculo,
    CAST(Aggr.TotalDeSegundos / 3600 AS VARCHAR) + ':' +
    CAST((Aggr.TotalDeSegundos % 3600) / 60 AS VARCHAR) + ':' +
    CAST((Aggr.TotalDeSegundos % 60) AS VARCHAR) AS 'HH:MM:SS'
FROM
(
    SELECT
        ModeloVeiculo,
        SUM(ISNULL(DATEDIFF(SECOND,DataEntradaManutencao,DataSaidaManutencao),0)) TotalDeSegundos
    FROM
        @ManutencaoVeiculos
    GROUP BY
        ModeloVeiculo

)
AS
    Aggr
ORDER BY
    2
DESC

Com isso descobrimos que o VeiculoDDD, foi o veiculo que ficou 396 horas, 42 minutos e 0 segundos parado em manutenção. Trabalhar com horas não é mais difícil depois que se aprende a manipular com estas técnicas

2 comentários em “Agregando horas no SQL”

  1. E aí Vitão, blz?

    Curti o que você postou e resolvi dar uma brincada em cima disso. Primeiro fiz um script pra quem conheceu um pouco de T-SQL, mas tem preguiça de pensar rs… Aí vai:

    https://dl.dropbox.com/u/20361010/blog/vitor_mendes/agregando-horas-no-sql_modo_preguica_de_pensar.sql

    Depois pensei numa forma meio ninja de não usar nenhuma função interna a não ser o CAST, o que geralmente torna o código mais performático e melhor pra rodar em bases gigantescas. Aí vai:

    https://dl.dropbox.com/u/20361010/blog/vitor_mendes/agregando-horas-no-sql_modo_performatico_ninja.sql

    Falta do que fazer, né? rs…

    Abraço.

    1. Não diga falta do que fazer, mais sim uma forma de usar a cabeça de verdade. Por incrível que pareça a primeira solução que você mostrou foi a que me sugeriram e não contente com a solução acabei pesquisando por soluções melhores e a que mostrei acima se encaixou perfeitamente.

      A forma de obter horas ninja sem comentários, acabo me recordando daquele seu post sobre a melhor forma de extrair horas de um DATETIME, muitos não ligam para performance, só quem trabalhou com servidores ruim para processamento de dados sabe como 1 mile-segundo faz toda a diferença.

      http://www.emilaneze.com.br/post/2011/09/23/SQL-Server-Removendo-a-hora-de-um-DATETIME.aspx

      Abraço

Opine, discuta, participe ou simplesmente 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 )

w

Conectando a %s