Calcular a diferença entre horas utilizando SQL
Neste artigo vamos ver como resolver um problema muito comum, que é o calculo da diferença de horários entre duas datas. Vamos ver exemplos utilizando o MySQL e o SQL Server.
Você vai ver como calcular a diferença de horas e também como formatar os resultados para exibição.
Recursos que serão utilizados neste artigo:
No MySQL:
TIMESTAMPDIFF
DATE_FORMAT
SEC_TO_TIME
WEEKDAY
CASE
No SQL Server:
DATEDIFF
FORMAT
DATEADD
CAST
DATEPART
CASE
Nosso primeiro passo é criar uma tabela de testes e adicionar registros.
MySQL
CREATE TABLE horarios (
id int NOT NULL AUTO_INCREMENT PRIMARY KEY,
nome VARCHAR(50),
horario_entrada DATETIME,
almoco_inicio DATETIME,
almoco_fim DATETIME,
horario_saida DATETIME
);
SQL Server
CREATE TABLE horarios (
id int IDENTITY(1,1) PRIMARY KEY,
nome VARCHAR(50),
horario_entrada DATETIME,
almoco_inicio DATETIME,
almoco_fim DATETIME,
horario_saida DATETIME
);
INSERT INTO horarios (nome, horario_entrada, almoco_inicio, almoco_fim, horario_saida) VALUES ('Daniel', '2022-06-15 07:59:00', '2022-06-15 12:00:00', '2022-06-15 13:00:00', '2022-06-15 18:00:00');
INSERT INTO horarios (nome, horario_entrada, almoco_inicio, almoco_fim, horario_saida) VALUES ('Lucas', '2022-06-15 08:59:00', '2022-06-15 12:00:00', '2022-06-15 13:00:00', '2022-06-15 17:20:00');
INSERT INTO horarios (nome, horario_entrada, almoco_inicio, almoco_fim, horario_saida) VALUES ('Matheus', '2022-06-15 08:09:00', '2022-06-15 12:10:00', '2022-06-15 12:59:00', '2022-06-15 18:00:00');
INSERT INTO horarios (nome, horario_entrada, almoco_inicio, almoco_fim, horario_saida) VALUES ('Thiago', '2022-06-15 08:19:00', '2022-06-15 12:00:00', '2022-06-15 13:00:00', '2022-06-15 18:00:00');
INSERT INTO horarios (nome, horario_entrada, almoco_inicio, almoco_fim, horario_saida) VALUES ('João', '2022-06-15 07:15:00', '2022-06-15 11:30:00', '2022-06-15 12:15:00', '2022-06-15 18:10:00');
Calculando a diferença entre dois horários
Agora que criamos a nossa tabela de testes, vamos fazer o cálculo das horas trabalhadas. Para isso, precisamos calcular o tempo decorrido entre o horário de entrada e o horário de saída e substrair o tempo de almoço.
Para calcular a diferença entre os horários no MySQL vamos utilizar a função TIMESTAMPDIFF. No exemplo a seguir calculamos a diferença em minutos entre o horário de entrada e o horário de saída.
SELECT nome, horario_entrada, horario_saida, TIMESTAMPDIFF(MINUTE, horario_entrada, horario_saida) as jornada from horarios
No SQL Server utilizamos a função DATEDIFF para obter o mesmo resultado
SELECT nome, horario_entrada, horario_saida, DATEDIFF(MINUTE, horario_entrada, horario_saida) as jornada from horarios
Perceba que ambas as funções recebem os mesmo parâmetros, o primeiro é o retorno desejado, que em nosso caso são minutos, o intervalo de tempo inicial e o intervalo de tempo final.
Formatando a exibição de datas e de horas
Para deixar o nosso relatório melhor, vamos colocar a data formatada no formato brasileiro. No MySQL utilizamos a função DATE_FORMAT
SELECT nome, DATE_FORMAT (horario_entrada,'%d/%m/%Y') as data, horario_entrada, horario_saida, TIMESTAMPDIFF(MINUTE, horario_entrada, horario_saida) as jornada from horarios
Já no SQL Server utilizamos a função FORMAT
SELECT nome, FORMAT(horario_entrada, 'dd/MM/yyyy') as data, horario_entrada, horario_saida, DATEDIFF(MINUTE, horario_entrada, horario_saida) as jornada from horarios
Ambas as funções recebem dois parâmetros, o primeiro é a data e o segundo é o formato de exibição desejado.
Para extrair o horário de um campo DATETIME no MySQL vamos utilizar a função TIME. Vamos corrigir a seguir a exibição da hora de entrada e da hora de saída.
SELECT nome, DATE_FORMAT (horario_entrada,'%d/%m/%Y') as data, TIME(horario_entrada) as horario_entrada, TIME(horario_saida) as horario_saida, TIMESTAMPDIFF(MINUTE, horario_entrada, horario_saida) as jornada from horarios
No SQL Server se utiliza a função FORMAT
SELECT nome, FORMAT(horario_entrada, 'dd/MM/yyyy') as data, FORMAT(horario_entrada, 'HH:mm:ss') AS horario_entrada, FORMAT(horario_saida, 'HH:mm:ss') as horario_saida, DATEDIFF(MINUTE, horario_entrada, horario_saida) as jornada from horarios
Ajustando o cálculo da jornada de trabalho
Perceba que para calcular a jornada de trabalho de forma correta precisamos subtrair o tempo de almoço. Vamos alterar a nossa consulta e fazer esta operação:
MySQL
SELECT nome, DATE_FORMAT (horario_entrada,'%d/%m/%Y') as data, TIME(horario_entrada) as horario_entrada, TIME(horario_saida) as horario_saida, TIMESTAMPDIFF(MINUTE, horario_entrada, horario_saida) - TIMESTAMPDIFF(MINUTE, almoco_inicio, almoco_fim) as jornada from horarios
SQL Server
SELECT nome, FORMAT(horario_entrada, 'dd/MM/yyyy') as data, FORMAT(horario_entrada, 'HH:mm:ss') AS horario_entrada, FORMAT(horario_saida, 'HH:mm:ss') as horario_saida,
DATEDIFF(MINUTE, horario_entrada, horario_saida) - DATEDIFF(MINUTE, almoco_inicio, almoco_fim) as jornada
from horarios
Exibindo a jornada de trabalho no formato horas:minutos
Será necessário melhorar a exibição deste resultado. Ao invés de mostrar o resultado em minutos, vamos mostrar o resultado formatado em horas:minutos. No MySQL podemos utilizar a função SEC_TO_TIME para converter um número inteiro representando segundos num horário. No nosso caso, vamos precisar alterar o retorno da função TIMESTAMPDIFF de MINUTE para SECOND.
SELECT nome, DATE_FORMAT (horario_entrada,'%d/%m/%Y') as data, TIME(horario_entrada) as horario_entrada, TIME(horario_saida) as horario_saida, SEC_TO_TIME(TIMESTAMPDIFF(SECOND, horario_entrada, horario_saida) - TIMESTAMPDIFF(SECOND, almoco_inicio, almoco_fim)) as jornada from horarios
No SQL Server vamos utilizar a função DATEADD para criar uma data com base nos minutos calculados, e a função CAST para converter o resultado num horário. DATEADD recebe três parâmetros, o primeiro vai ser o intervalo de tempo que vamos adicionar, que é em minutos, e o segundo vai ser o calculo que fizemos para calcular a jornada de trabalho em minutos, e o terceiro é a data que vai receber o acréscimo. Como desejamos apenas o horário, o terceiro parâmetro vai ser zero.
A função CAST vai fazer a conversão de tipos de dados. Ela recebe uma expressão e faz a conversão para um determinado tipo de dados. Em nosso caso, ela recebe o retorno da funçao DATEADD que está no formato DATETIME e converte para o tipo de dados TIME.
SELECT nome, FORMAT(horario_entrada, 'dd/MM/yyyy') as data, FORMAT(horario_entrada, 'HH:mm:ss') AS horario_entrada, FORMAT(horario_saida, 'HH:mm:ss') as horario_saida,
CAST(DATEADD(MINUTE, DATEDIFF(MINUTE, horario_entrada, horario_saida) - DATEDIFF(MINUTE, almoco_inicio, almoco_fim),0) AS TIME(0)) as jornada
from horarios
Acrescentando o dia da semana
Para concluir, vamos acrescentar em nossa consulta mais uma informação, que é o dia da semana.
No MySQL a função WEEKDAY recebe como parâmetro uma data e retorna um número inteiro representando o dia da semana, sendo 0 para segunda-feira e 6 para domingo.
SELECT nome, DATE_FORMAT (horario_entrada,'%d/%m/%Y') as data, WEEKDAY(horario_entrada) as dia_semana from horarios
O retorno do campo dia_semana foi 2 porque o dia 15/06/2022 foi uma quarta-feira.
No SQL Server podemos fazer o mesmo utilizando a função DATEPART
SELECT nome, FORMAT(horario_entrada, 'dd/MM/yyyy') as data, DATEPART(WEEKDAY, horario_entrada) as dia_semana from horarios
No SQL Server o número inteiro representando o dia da semana começa com 1 para domingo e 7 para sábado.
O retorno do campo dia_semana foi 4 porque o dia 15/06/2022 foi uma quarta-feira.
Para exibir o nome do dia da semana vamos precisar utilizar CASE. O CASE permite retornar o nome do dia da semana de acordo com o número retornado. O CASE existe tanto no MySQL quanto no SQL Server.
MySQL
SELECT nome, DATE_FORMAT (horario_entrada,'%d/%m/%Y') as data, (CASE WEEKDAY(horario_entrada)
when 0 then 'Segunda-feira'
when 1 then 'Terça-feira'
when 2 then 'Quarta-feira'
when 3 then 'Quinta-feira'
when 4 then 'Sexta-feira'
when 5 then 'Sábado'
when 6 then 'Domingo'
END) as dia_semana from horarios
SQL Server
SELECT nome, FORMAT(horario_entrada, 'dd/MM/yyyy') as data, (CASE DATEPART(WEEKDAY, horario_entrada)
when 2 then 'Segunda-feira'
when 3 then 'Terça-feira'
when 4 then 'Quarta-feira'
when 5 then 'Quinta-feira'
when 6 then 'Sexta-feira'
when 7 then 'Sábado'
when 1 then 'Domingo'
END) as dia_semana from horarios
Encerramento
Nossa consulta completa com todos os recursos apresentando neste artigo fica da seguinte forma:
MySQL
SELECT nome, DATE_FORMAT (horario_entrada,'%d/%m/%Y') as data, (CASE WEEKDAY(horario_entrada)
when 0 then 'Segunda-feira'
when 1 then 'Terça-feira'
when 2 then 'Quarta-feira'
when 3 then 'Quinta-feira'
when 4 then 'Sexta-feira'
when 5 then 'Sábado'
when 6 then 'Domingo'
END) as dia_semana, TIME(horario_entrada) as horario_entrada, TIME(horario_saida) as horario_saida, SEC_TO_TIME(TIMESTAMPDIFF(SECOND, horario_entrada, horario_saida) - TIMESTAMPDIFF(SECOND, almoco_inicio, almoco_fim)) as jornada from horarios
SQL Server
SELECT nome, FORMAT(horario_entrada, 'dd/MM/yyyy') as data, (CASE DATEPART(WEEKDAY, horario_entrada)
when 2 then 'Segunda-feira'
when 3 then 'Terça-feira'
when 4 then 'Quarta-feira'
when 5 then 'Quinta-feira'
when 6 then 'Sexta-feira'
when 7 then 'Sábado'
when 1 then 'Domingo'
END) as dia_semana, FORMAT(horario_entrada, 'HH:mm:ss') AS horario_entrada, FORMAT(horario_saida, 'HH:mm:ss') as horario_saida,
CAST(DATEADD(MINUTE, DATEDIFF(MINUTE, horario_entrada, horario_saida) - DATEDIFF(MINUTE, almoco_inicio, almoco_fim),0) AS TIME(0)) as jornada
from horarios
