Como realizar cálculos com horas no SQL Server – Parte III

Olá Pessoal,

Nos artigos anteriores demonstrei como realizar cálculos com horas no SQL Server. Os dois artigos tinham uma característica em comum. Todas as situações expostas informavam determinados momentos no tempo e era necessário calcular a diferença entre esses tempos. Existem algumas situações em que o momento no tempo é desconhecido ou ainda irrelevante. Se um serviço é contratado para durar quarenta horas ou ainda se dispõe de 40 horas de um determinado profissional, o único dado conhecido é a quantidade de 40 horas. Nessa situação, a quantidades de horas é diretamente lançada e as abordagens anteriores não podem ser aplicadas (ou tem de ser pelo menos adaptadas). Embora talvez pareça um problema simples, complexidades podem surgir quando existe a presença de outras unidades (minutos ou segundos por exemplo). Nesse artigo trabalharei essas situações.

Os exemplos irão avaliar um projeto da empresa XYZ Ltda. Essa empresa está realizando um estudo externo para medir a eficiência de alguns departamentos e seus processos internos. Antes de propriamente realizar qualquer proposta ou intervenção, é necessário saber as principais tarefas de cada um dos departamentos avaliados bem como o tempo gasto nesses processos. Para evitar possíveis distorções, os processos avaliados são repetidos e seu tempo contabilizado diversas vezes. Com isso pode ser obtido um tempo médio, desvios padrões, etc. Os processos avaliados são o de curta duração e não é admissível que demorem mais do que um dia. A proposta de modelagem física é exposta abaixo:

CREATE TABLE Deptos (IDDepto INT, NomeDepto VARCHAR(50))
CREATE TABLE Processos (IDProcesso INT, IDDepto INT, NomeTarefa VARCHAR(200))
CREATE TABLE Medicoes (IDMedicao INT, IDProcesso INT, Duracao ?)

A escolha do tipo de dados

Se observarmos o script proposto veremos que a coluna "Duracao" está com um tipo dados indeterminado (inclusive o script falha se for executado). Qual seria o tipo de dados correto ? Já vi implementações do tipo CHAR(8), DATETIME ou no caso do SQL Server 2008 a utilização do tipo TIME. O tipo CHAR(8) representa o formato hh:mm:ss mas definitivamente é uma péssima escolha. Armazenar uma duração de um processo com o tipo CHAR(8) irá causar vários problemas uma vez que embora o formato seja perfeitamente compreendido por pessoas, nenhum banco de dados saberá que trata-se de uma duração. Todos os cálculos irão provocar uma conversão desse tipo de dados para outro tipo de dados de forma a tornar os cálculos possíveis (não é possível fazer cálculos matemáticos com CHAR diretamente) e isso provocará um overhead de conversões além de possíveis problemas com a indexação. Assim sendo, tendo em vista os problemas estou desconsiderando essa possibilidade. Resta então o clássico SMALLDATETIME, tipos inteiros e o TIME do SQL Server 2008. Vejamos então as possibilidades:

Horários com base em colunas do tipo SMALLDATETIME

O tipo de dados SMALLDATETIME armazena uma data seguida de um horário. Como deseja-se apenas o horário, ignora-se a data (possivelmente será assumido 01/01/1900) e lança-se o horário diretamente. Ex:

CREATE TABLE Deptos (IDDepto INT, NomeDepto VARCHAR(50))
CREATE TABLE Processos (IDProcesso INT, IDDepto INT, NomeProcesso VARCHAR(200))
CREATE TABLE Medicoes (IDMedicao INT, IDProcesso INT, Duracao SMALLDATETIME)

INSERT INTO Deptos VALUES (1,‘RH’)
INSERT INTO Deptos VALUES (2,‘Financeiro’)

INSERT INTO Processos VALUES (1,1,‘Avaliar Currículos’)
INSERT INTO Processos VALUES (2,1,‘Efetuar Cálculos Trabalhistas’)
INSERT INTO Processos VALUES (3,1,‘Realizar Provas Práticas’)
INSERT INTO Processos VALUES (4,2,‘Conciliar Pagamentos Bancários’)
INSERT INTO Processos VALUES (5,2,‘Conferir Ordens de Pagamento’)

INSERT INTO Medicoes VALUES (01,1,’04:00:00′)
INSERT INTO Medicoes VALUES (02,1,’05:30:00′)
INSERT INTO Medicoes VALUES (03,1,’03:45:00′)
INSERT INTO Medicoes VALUES (04,2,’15:00:00′)
INSERT INTO Medicoes VALUES (05,2,’13:35:00′)
INSERT INTO Medicoes VALUES (06,3,’08:00:00′)
INSERT INTO Medicoes VALUES (07,4,’40:00:00′)
INSERT INTO Medicoes VALUES (08,4,’36:00:00′)
INSERT INTO Medicoes VALUES (09,4,’37:25:00′)
INSERT INTO Medicoes VALUES (10,4,’38:15:00′)
INSERT INTO Medicoes VALUES (11,5,’12:10:00′)
INSERT INTO Medicoes VALUES (12,5,’13:20:00′)

Ao executar o script, algumas registros retornam a seguinte mensagem de erro

Msg 296, Level 16, State 3, Line 20
The conversion of char data type to smalldatetime data type resulted in an out-of-range smalldatetime value.

The statement has been terminated.

O primeiro registro a provocar essa mensagem de erro é o referente a 40 horas. A string "40:00:00" é um horário válido e perfeitamente aceitável (a medição retornou 40 horas), mas o tipo de dados é SMALLDATETIME e nesse caso, não é tolerado a existência de um valor de 40 horas. É necessário entender que se trata de 1 dia (24 horas) e mais 16 horas restantes. Esse é um dos problemas graves de se utilizar SMALLDATETIME (ou DATETIME) para essa situação. Tais tipos de dados são excelentes para registrar-se um momento, mas não um intervalo. Para que esses tipos continuem a ser utilizados, é necessário algumas adaptações.

— Exclui os registros anteriores
DELETE FROM Medicoes

— Insere os registros no novo formato
INSERT INTO Medicoes VALUES (01,1,‘19000101 04:00:00’)
INSERT INTO Medicoes VALUES (02,1,‘19000101 05:30:00’)
INSERT INTO Medicoes VALUES (03,1,‘19000101 03:45:00’)
INSERT INTO Medicoes VALUES (04,2,‘19000101 15:00:00’)
INSERT INTO Medicoes VALUES (05,2,‘19000101 13:35:00’)
INSERT INTO Medicoes VALUES (06,3,‘19000101 08:00:00’)
INSERT INTO Medicoes VALUES (07,4,‘19000102 16:00:00’)
INSERT INTO Medicoes VALUES (08,4,‘19000102 12:00:00’)
INSERT INTO Medicoes VALUES (09,4,‘19000102 13:25:00’)
INSERT INTO Medicoes VALUES (10,4,‘19000102 14:15:00’)
INSERT INTO Medicoes VALUES (11,5,‘19000101 12:10:00’)
INSERT INTO Medicoes VALUES (12,5,‘19000101 13:20:00’)

O novo formato permite que a duração das medições seja inserida, mas já é possível perceber alguns inconvenientes. Durações superiores a 24 horas irão exigir uma certa inteligência para "calcular" o dia e a hora correta de forma a representar a duração desejada. É um problema considerável, mas ainda assim não inviabiliza completamente a utilização desse tipo de dados. Se a referência (data zero) é 01/01/1900, pode-se subtrair essa data da coluna duração e obter uma diferença em minutos através da função DATEDIFF.

— Calcula duração em minutos
;WITH Duracoes (IDMedicao, IDProcesso, DuracaoMin)
AS (
    SELECT IDMedicao, IDProcesso, DATEDIFF(MI,‘19000101’,Duracao)
    FROM Medicoes)

— Mostra o total de contagens e uma média
SELECT
    NomeDepto, NomeProcesso, COUNT(IDMedicao) As Quantidade,
    AVG(DuracaoMin) As Media
FROM
    Deptos
    INNER JOIN Processos ON Deptos.IDDepto = Processos.IDDepto
    INNER JOIN Duracoes ON Processos.IDProcesso = Duracoes.IDProcesso
GROUP BY
    NomeDepto, NomeProcesso

Os resultados são expressos em minutos. Pode ser desejável apresentá-los no formato hh:mm por exemplo. Algumas adaptações podem tornar isso possível:

— Calcula duração em minutos
;WITH Duracoes (IDMedicao, IDProcesso, DuracaoMin)
AS (
    SELECT IDMedicao, IDProcesso, DATEDIFF(MI,‘19000101’,Duracao)
    FROM Medicoes),

Resultados As (

— Mostra o total de contagens e uma média
SELECT
    NomeDepto, NomeProcesso, COUNT(IDMedicao) As Quantidade,
    AVG(DuracaoMin) As Media
FROM
    Deptos
    INNER JOIN Processos ON Deptos.IDDepto = Processos.IDDepto
    INNER JOIN Duracoes ON Processos.IDProcesso = Duracoes.IDProcesso
GROUP BY
    NomeDepto, NomeProcesso)

— Formata os resultados
SELECT NomeDepto, NomeProcesso, Quantidade,

    RIGHT(‘0’ + CAST(Media / 60 As VARCHAR(2)),2) + ‘:’ +
    RIGHT(‘0’ + CAST(Media % 60 As VARCHAR(2)),2) As DuracaoMedia

FROM Resultados

O resultado é exibido na tabela abaixo:

Depto

Processo

Quantidade

Duração Média

Financeiro

Conciliar Pagamentos Bancários

4

37:55

Financeiro

Conferir Ordens de Pagamento

2

12:45

RH

Avaliar Currículos

3

04:25

RH

Efetuar Cálculos Trabalhistas

2

14:17

RH

Realizar Provas Práticas

1

08:00

Horários com base em colunas do tipo inteiro

Essa abordagem é simples, direta e não sei porque razão é a menos escolhida por muitos administradores de dados (às vezes sequer é pensada por eles). Parece que os tipos de dados relacionados a tempo são preferidos e representam automaticamente a única alternativa (sem dúvida uma visão bem distorcida). Vejamos a abordagem com base em inteiros.

— Exclui a tabela de Medicoes
DROP TABLE Medicoes

— Recria a tabela
CREATE TABLE Medicoes (IDMedicao INT, IDProcesso INT, Horas TINYINT, Minutos TINYINT)

— Insere os registros
INSERT INTO Medicoes VALUES (01,1,04,00)
INSERT INTO Medicoes VALUES (02,1,05,30)
INSERT INTO Medicoes VALUES (03,1,03,45)
INSERT INTO Medicoes VALUES (04,2,15,00)
INSERT INTO Medicoes VALUES (05,2,13,35)
INSERT INTO Medicoes VALUES (06,3,08,00)
INSERT INTO Medicoes VALUES (07,4,40,00)
INSERT INTO Medicoes VALUES (08,4,36,00)
INSERT INTO Medicoes VALUES (09,4,37,25)
INSERT INTO Medicoes VALUES (10,4,38,15)
INSERT INTO Medicoes VALUES (11,5,12,10)
INSERT INTO Medicoes VALUES (12,5,13,20)

Os cálculos ficam muito mais simples que a abordagem anterior (até o armazenamento fica mais reduzido, pois, o TINYINT ocupa um byte).

;WITH Resultados As (
SELECT
    NomeDepto, NomeProcesso, COUNT(IDMedicao) As Quantidade,
    AVG((Horas * 60) + Minutos) As Media
FROM
    Deptos
    INNER JOIN Processos ON Deptos.IDDepto = Processos.IDDepto
    INNER JOIN Medicoes ON Processos.IDProcesso = Medicoes.IDProcesso
GROUP BY
    NomeDepto, NomeProcesso)

— Formata os resultados
SELECT NomeDepto, NomeProcesso, Quantidade,

    RIGHT(‘0’ + CAST(Media / 60 As VARCHAR(2)),2) + ‘:’ +
    RIGHT(‘0’ + CAST(Media % 60 As VARCHAR(2)),2) As DuracaoMedia

FROM Resultados

Horários com base no tipo de dados TIME do SQL Server 2008

A abordagem com o tipo de dados TIME é bem semelhante a do tipo SMALLDATETIME.

— Exclui a tabela
DROP TABLE Medicoes

— Recria a tabela
CREATE TABLE Medicoes (IDMedicao INT, IDProcesso INT, Duracao TIME(0))

— Insere os registros
INSERT INTO Medicoes VALUES (01,1,’04:00:00′)
INSERT INTO Medicoes VALUES (02,1,’05:30:00′)
INSERT INTO Medicoes VALUES (03,1,’03:45:00′)
INSERT INTO Medicoes VALUES (04,2,’15:00:00′)
INSERT INTO Medicoes VALUES (05,2,’13:35:00′)
INSERT INTO Medicoes VALUES (06,3,’08:00:00′)
INSERT INTO Medicoes VALUES (07,4,’40:00:00′)
INSERT INTO Medicoes VALUES (08,4,’36:00:00′)
INSERT INTO Medicoes VALUES (09,4,’37:25:00′)
INSERT INTO Medicoes VALUES (10,4,’38:15:00′)
INSERT INTO Medicoes VALUES (11,5,’12:10:00′)
INSERT INTO Medicoes VALUES (12,5,’13:20:00′)

Entretanto, embora sintaticamente correto, a execução desse script gera um erro

Msg 241, Level 16, State 1, Line 11
Conversion failed when converting date and/or time from character string.

O erro é gerado na linha cujo ID é 7 e duração de 40 horas. A string "40:00:00" representa 40 horas, mas o tipo de dados TIME não foi preparado para essa representação. Não se trata de uma limitação, mas sim de um propósito. O tipo de dados TIME deve representar um momento no tempo (apenas da parte horária desconsiderando a data) e não uma quantidade de horas. A abordagem com o tipo de dados SMALLDATETIME possui o mesmo sentido, mas caso a quantidade de horas fosse superior a 24, bastaria adicionar um dia a mais. A abordagem com o tipo de dados TIME é análoga, mas não pode utilizar esse artifício e portanto, caso haja uma quantidade de horas superior à 24, esse tipo de dados é inapropriado. Supondo a retirada do processo 4, é possível verificar como se comporta a utilização do tipo de dados TIME.

— Exclui os registros anteriores
DELETE FROM Medicoes

— Insere os registros
INSERT INTO Medicoes VALUES (01,1,’04:00:00′)
INSERT INTO Medicoes VALUES (02,1,’05:30:00′)
INSERT INTO Medicoes VALUES (03,1,’03:45:00′)
INSERT INTO Medicoes VALUES (04,2,’15:00:00′)
INSERT INTO Medicoes VALUES (05,2,’13:35:00′)
INSERT INTO Medicoes VALUES (06,3,’08:00:00′)
INSERT INTO Medicoes VALUES (11,5,’12:10:00′)
INSERT INTO Medicoes VALUES (12,5,’13:20:00′)

As consultas utilizam a mesma lógica do tipo de dados SMALLDATETIME, ou seja, utiliza-se um valor base para calcular a diferença. Com o tipo de dados SMALLDATETIME, utilizou-se a data de 01/01/1900 (mais precisamente 01/01/1900 00:00). No caso do tipo de dados TIME, utilizá-se como referência o horário 00:00:

— Calcula duração em minutos
;WITH Duracoes (IDMedicao, IDProcesso, DuracaoMin)
AS (
    SELECT IDMedicao, IDProcesso, DATEDIFF(MI,’00:00:00′,Duracao)
    FROM Medicoes),

Resultados As (

— Mostra o total de contagens e uma média
SELECT
    NomeDepto, NomeProcesso, COUNT(IDMedicao) As Quantidade,
    AVG(DuracaoMin) As Media
FROM
    Deptos
    INNER JOIN Processos ON Deptos.IDDepto = Processos.IDDepto
    INNER JOIN Duracoes ON Processos.IDProcesso = Duracoes.IDProcesso
GROUP BY
    NomeDepto, NomeProcesso)

— Formata os resultados
SELECT NomeDepto, NomeProcesso, Quantidade,

    RIGHT(‘0’ + CAST(Media / 60 As VARCHAR(2)),2) + ‘:’ +
    RIGHT(‘0’ + CAST(Media % 60 As VARCHAR(2)),2) As DuracaoMedia

FROM Resultados

O resultado é exibido na tabela abaixo:

Depto

Processo

Quantidade

Duração Média

Financeiro

Conferir Ordens de Pagamento

2

12:45

RH

Avaliar Currículos

3

04:25

RH

Efetuar Cálculos Trabalhistas

2

14:17

RH

Realizar Provas Práticas

1

08:00

Como pode ser percebido, o tipo de dados TIME retorna os mesmos dados do SMALLDATETIME, mas com a limitação de 23:59 no máximo antes de gerar uma falha de conversão.

Juntamente com os dois artigos anteriores, a idéia é realmente fazer um overview de como realizar cálculos que envolvam o tempo no SQL Server. De forma nenhuma o assunto está esgotado, até porque existem outras unidades temporais que não foram trabalhadas (dias, segundos, etc) e algumas outras alternativas que não foram apresentadas. Os artigos já servem como um ponto de partida para cálculos horários mais complexos.

[ ]s,

Gustavo

Deixe uma resposta

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 )

Imagem do Twitter

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

Foto do Facebook

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

Foto do Google+

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

Conectando a %s