SQL

Calcular a diferença entre horas utilizando SQL

19/06/2022SQL

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

 

 

Outros conteudos que podem ser de seu interesse

Consultar uma API de CEP usando SQL Server
16/02/2020SQL

Consultar uma API de CEP usando SQL Server

Veja como consumir uma API de CEP com o SQL Server

Saiba mais...
Como localizar queries lentas no SQL Server
23/09/2019SQL

Como localizar queries lentas no SQL Server

Aprenda como localizar as queries que deixam o seu banco de dados lento

Saiba mais...

Conteúdo sobre banco de dados sem complicação!

MySQL - Referência Rápida

 

SQL Server - Referência Rápida

 

SQL vs Mongo