Crie um site como este com o WordPress.com
Comece agora

Horário de verão confunde datediff()

Neste ano de 2018 o horário de verão inicia mais tarde, em 4 de novembro. Os impactos em nosso dia a dia já conhecemos, mas há um efeito colateral que pode não ter sido percebido na programação de computadores, em especial para aqueles eventos que cruzam o início e/ou fim do horário de verão. Que o diga a função datediff()…

1. Introdução

Em 2017, ao responder a uma questão no fórum Transact-SQL do Microsoft TechNet, deparei com uma situação curiosa sobre a função DATEDIFF(). E há algumas semanas, ao ler post em um blog, me lembrei daquele problema pois de certa forma a solução definida em ambos envolveu o mesmo recurso. Para discorrer sobre a situação de 2017 neste artigo será utilizado um conjunto de tabelas para armazenar marcações de relógio de ponto fictício.

2. O problema

Neste sistema de ponto eletrônico ocorre armazenamento do horário de entrada e de saída do funcionário, para cada turno, utilizando coluna com tipo de dados datetime2, principalmente para turnos que iniciam em um dia e terminam no dia seguinte. Para calcular o tempo em que o funcionário trabalhou em cada turno basta então utilizar a função datediff().

Supondo que a tabela Batida_Ponto registre entradas e saídas, contendo a seguinte estrutura:

-- código #1
USE tempDB;
go

CREATE TABLE dbo.Batida_Ponto (
   Chapa char(4) not null,
   Horário datetime2(0) not null,
   Evento char(1) not null check (Evento in ('E', 'S'))
);

e que o funcionário de registro 1835 tenha como turno de trabalho o horário das 22h de um dia às 4h do dia seguinte, com descanso às segundas-feiras. Para um determinado dia ele teria o seguinte registro no ponto eletrônico:

-- código #2
set dateformat dmy;
INSERT into dbo.Batida_Ponto (Chapa, Horário, Evento) values
     ('1835', '16/2/2018 21:55', 'E'),
     ('1835', '17/2/2018 4:05', 'S');

O cálculo do horário trabalhado pode ser efetuado assim:

-- código #3
with agrupaHorário as (
SELECT Chapa, Horário as Horário_1, Evento as Evento_1,
       lead(Horário) over (partition by Chapa 
                           order by Horário) as Horário_2,
       lead(Evento) over (partition by Chapa 
                           order by Horário) as Evento_2
   from dbo.Batida_Ponto
)
SELECT Chapa,
       Horário_1 as Horário_Entrada,
       Horário_2 as Horário_Saída,
       datediff(minute, Horário_1, Horário_2) as Trabalhado
   from agrupaHorário
   where Evento_1 = 'E'
         and Evento_2 = 'S';

que apresenta o seguinte resultado:

p006_codigo#3_x1

Um dia se passa e mais um dia o funcionário trabalha:

-- código #4
set dateformat dmy;
INSERT into dbo.Batida_Ponto (Chapa, Horário, Evento) values
      ('1835', '17/2/2018 22:02', 'E'),
      ('1835', '18/2/2018 4:05', 'S');

Ao reexecutar o código #3 temos:

p006_codigo#3_x2

O funcionário foi ao Departamento de Pessoal e reclamou que o cálculo para o turno do dia 17 não estava correto. Segundo ele, no turno iniciado no dia 16 ele trabalhou 6h10min (equivalente a 370 minutos) e no turno iniciado no dia 17 ele trabalhou 7h03min (equivalente a 423 minutos). E fez as contas na ponta do lápis, provando que estava certo. Se o funcionário estava correto, então a apuração do relógio de ponto falhou?

O detalhe é que no dia 18 de fevereiro de 2018 ocorreu o encerramento do horário de verão no Brasil. Desta forma, às 0:00 do dia 18/2/2018 os relógios tiveram que ser atrasados em uma hora. Ou seja, voltou a ser 17/2/2018 23:00. Assim, no dia 17/2/2018 quem trabalhou no turno das 22h às 4h teve que trabalhar uma hora a mais.

O sistema operacional do computador estava programado para ajustar automaticamente o relógio tanto no início quanto no final do horário de verão. Foi uma falha na análise do problema ou na programação? Ou da equipe de suporte, que não desativou a atualização automática de horário de verão? O certo é que não se previu o impacto do horário de verão no cálculo de minutos trabalhados no turno.

3. Possíveis soluções em T-SQL

A seguir algumas possíveis soluções do contexto do gerenciador de banco de dados. Necessariamente o cálculo da diferença não necessita ser realizado em T-SQL, podendo ser no aplicativo. Mas, para fins de demonstração do problema, a seguir algumas possíveis soluções em T-SQL.

3.1. AT TIME ZONE

Uma das formas de evitar tal falha no cálculo de horas trabalhadas é utilizar AT TIME ZONE, onde basta substituir a linha

datediff(minute, Horário_1, Horário_2)

por

datediff(minute,
         Horário_1 at time zone 'E. South America Standard Time',
         Horário_2 at time zone 'E. South America Standard Time')

O código #3 seria então reescrito para:

-- código #12
with agrupaHorário as (
SELECT Chapa, Horário as Horário_1, Evento as Evento_1,
       lead(Horário) over (partition by Chapa 
                           order by Horário) as Horário_2,
       lead(Evento) over (partition by Chapa 
                           order by Horário) as Evento_2
   from dbo.Batida_Ponto
)
SELECT Chapa,
       Horário_1 as Horário_Entrada,
       Horário_2 as Horário_Saída,
       datediff(minute,
       Horário_1 at time zone 'E. South America Standard Time',
       Horário_2 at time zone 'E. South America Standard Time') as Trabalhado
   from agrupaHorário
   where Evento_1 = 'E'
         and Evento_2 = 'S';

Ao executá-lo, temos o seguinte resultado:

p006_codigo#4
Simples, não?

A notícia ruim é que AT TIME ZONE somente funciona a partir da versão 2016 do SQL Server. 😞

3.2. Horário UTC

UTC é a sigla de Universal Time Coordinated, que é o fuso horário de referência a partir do qual se calculam todas as outras zonas horárias do mundo. Armazenar o horário UTC é outra solução que torna assim o horário independente de fuso horário e também de horário de verão.

-- código #5
TRUNCATE TABLE dbo.Batida_Ponto;
set dateformat dmy;
INSERT into dbo.Batida_Ponto (Chapa, Horário, Evento) values
     ('1835', '18/2/2018 0:02', 'E'),
     ('1835', '18/2/2018 7:05', 'S');

O horário de entrada, 17/2/2018 às 22h02, estava no fuso horário UTC-2; para converter para UTC basta somar 2 horas, obtendo-se então 18/2/2018 às 0h02min. Quanto ao horário de saída, 18/2/2018 às 4h05min, já estava no fuso horário UTC-3; para converter para UTC basta somar 3 horas, obtendo-se então 18/2/2018 às 7:05min.

No dia a dia, para obter o horário UTC diretamente do servidor SQL pode-se utilizar a função SYSUTCDATETIME(). Vide item Referências, ao final.

Ao executar o código #3 com os dados no horário UTC temos o seguinte resultado:

p006_codigo#3_x3

3.3. Horário TZ

Outra solução é armazenar o horário de batida junto com o fuso horário do momento em que ocorreu o evento, que é possível utilizando o tipo de dados datetimeoffset.

-- código #6
USE tempDB;
go
CREATE TABLE dbo.Batida_Ponto_TZ (
   Chapa char(4) not null,
   Horário datetimeoffset not null,
   Evento char(1) not null check (Evento in ('E','S'))
);
set dateformat dmy;
INSERT into dbo.Batida_Ponto_TZ (Chapa, Horário, Evento) values
     ('1835', '17/2/2018 22:02 -02:00', 'E'),
     ('1835', '18/2/2018 4:05 -03:00', 'S');

O horário de entrada, 17/2/2018 às 22h02, estava no fuso horário UTC-2; quanto ao horário de saída, 18/2/2018 às 4h05, estava no fuso horário UTC-3.

Para obter o horário com o fuso horário diretamente do servidor SQL pode-se utilizar a função SYSDATETIMEOFFSET(). Vide item Referências, ao final.

Ao executar o seguinte código (derivado do código #3):

-- código #7
with agrupaHorário as (
SELECT Chapa, Horário as Horário_1, Evento as Evento_1,
       lead(Horário) over (partition by Chapa 
                           order by Horário) as Horário_2,
       lead(Evento) over (partition by Chapa 
                           order by Horário) as Evento_2
   from dbo.Batida_Ponto_TZ
)
SELECT Chapa,
       Horário_1 as Horário_Entrada,
       Horário_2 as Horário_Saída,
       datediff(minute, Horário_1, Horário_2) as Trabalhado
   from agrupaHorário
    where Evento_1 = 'E'
          and Evento_2 = 'S';

retorna o seguinte resultado:

p006_codigo#7
Ou seja, temos o cálculo correto e também a informação de qual era o fuso horário em cada uma das datas.

3.4. Pacote SqlTimeZone

Existem opções de certa forma semelhantes a AT TIME ZONE e que funcionam nas versões anteriores a 2016 do SQL Server. No github está disponível o pacote SqlTimeZone, que contém funções para conversão de fuso horário e que levam em consideração o horário de verão. A documentação de utilização e os passos para a instalação do pacote SqlTimeZone estão na página do projeto, no github. Vide item Referências, ao final.

Para garantir o máximo de portabilidade, os testes deste item serão na versão 2008 do SQL Server. Como não há função lead() nesta versão, o código #3 foi reescrito inicialmente para:

-- código #8
with
tbEntrada as (
SELECT Chapa, Horário,
       Seq= row_number() over (partition by Chapa 
                               order by Horário)
   from dbo.Batida_Ponto
   where Evento = 'E'
),
tbSaída as (
SELECT Chapa, Horário,
       Seq= row_number() over (partition by Chapa 
                               order by Horário)
   from dbo.Batida_Ponto
   where Evento = 'S'
)
SELECT E.Chapa,
       E.Horário as Horário_Entrada,
       S.Horário as Horário_Saída,
       datediff(minute, E.Horário, S.Horário) as Trabalhado
   from tbEntrada as E
        inner join tbSaída as S on E.Chapa = S.Chapa 
                                   and E.Seq = S.Seq;

Para fins de demonstração, o código acima considera que não há nenhuma falha em batida de ponto. Isto é, para toda entrada há uma saída. Outras modificações serão efetuadas no código acima, à medida que desenvolvemos os testes.

Para o propósito deste artigo será utiliza a função Tzdb.LocalToUtc(), que converte um valor de horário local para o fuso horário UTC. Desta forma tem-se um horário que não varia com horários de verão, o que garante confiabilidade no uso da função DATEDIFF(). No código #8 será necessário acrescentar a conversão do horário local para o horário UTC, antes de efetuar o cálculo. Para facilitar a visualização do funcionamento, os horários de entrada e de saída serão exibidos no horário local e também no horário UTC.

-- código #8 v2
with
tbEntrada as (
SELECT Chapa, Horário,
       Horário_UTC =
              Tzdb.LocalToUtc(Horário, 'America/Sao_Paulo', 1, 1),
       Seq=row_number() over (partition by Chapa order by Horário)
   from dbo.Batida_Ponto
   where Evento = 'E'
),
tbSaída as (
SELECT Chapa, Horário,
       Horário_UTC= 
              Tzdb.LocalToUtc(Horário, 'America/Sao_Paulo', 1, 1),
       Seq=row_number() over (partition by Chapa order by Horário)
   from dbo.Batida_Ponto
   where Evento = 'S'
)
SELECT E.Chapa,
       E.Horário as Horário_Entrada,
       E.Horário_UTC as Horário_Entrada_UTC,
       S.Horário as Horário_Saída,
       S.Horário_UTC as Horário_Saída_UTC,
       Trabalhado= datediff(minute, E.Horário_UTC, S.Horário_UTC)
   from tbEntrada as E
        inner join tbSaída as S on E.Chapa = S.Chapa
                                   and E.Seq = S.Seq;

Após a execução do código acima, temos o seguinte resultado:

p006_codigo#8v2

Observa-se que para um mesmo horário local, 4h05min, no dia 17/2/2018 ele é convertido para 6h05min UTC e no dia seguinte para 7h05min UTC.

4. Considerações finais

Horário de verão é o período em que o relógio é adiantado em uma hora, em alguns Estados do Brasil, geralmente entre os meses de outubro de um ano ao mês de março do ano seguinte. Durante esse período o fuso horário (time zone) das regiões que o adotam é alterado.
Quando inicia o horário de verão ocorre um buraco de uma hora: não existe a faixa horária das 0h às 0h59min59seg. E quando o horário de verão encerra ocorre situação ainda pior, com sobreposição de horários, pois há duas faixas horárias contendo das 23h às 23h59min59seg: uma ainda durante o horário de verão e outra já no horário normal.

Para demonstrar o impacto do horário de verão nas soluções propostas no capítulo 3 será utilizado um conjunto de horários que ocorrem no último dia de horário de verão do ano de 2018.

-- código #9
CREATE TABLE Evento (
   Seq int identity,
   Horário datetimeoffset,
   Momento varchar(30)
);
set dateformat dmy;
INSERT into Evento (Horário, Momento) values
     ('14/10/2017 23:30 -03:00', 'antes horário de verão'),
     ('15/10/2017 01:00 -02:00', 'durante horário de verão'),
     ('17/2/2018 21:59 -02:00', 'durante horário de verão'),
     ('17/2/2018 22:00 -02:00', 'durante horário de verão'),
     ('17/2/2018 23:00 -02:00', 'durante horário de verão'),
     ('17/2/2018 23:30 -02:00', 'durante horário de verão'),
     ('17/2/2018 23:00 -03:00', 'após horário de verão'),
     ('17/2/2018 23:30 -03:00', 'após horário de verão'),
     ('18/2/2018 00:30 -03:00', 'após horário de verão');

No conjunto de horários acima aqueles que estão com fuso horário -02:00 são os relativos ao horário de verão e os com fuso horário -03:00 são aqueles registrados antes ou após o horário de verão. A primeira linha foi registrada antes do horário de verão e as próximas 5 linhas estão no horário de verão. Aí chegou meia noite e o relógio foi atrasado em uma hora, saindo do horário de verão. Então, 3 eventos foram registrados, após o horário de verão. Observe que para um mesmo dia há eventos registrados às 23h, o primeiro ainda no horário de verão e outro após o encerramento do horário de verão. O mesmo ocorre para o horário das 23h30min.

4.1. AT TIME ZONE e SqlTimeZone

Uma dos problemas que percebo nas soluções propostas utilizando AT TIME ZONE e SqlTimeZone é que o fuso horário fica hardcoded, o que não me parece ser uma boa prática.

Outro problema é o impacto negativo da sobreposição de faixas horárias no dia em que se encerra o horário de verão. Para exemplificar será utilizado o conjunto de horários da tabela Evento. Supondo que em uma tabela o horário seja declarado como datetime2(0), no código abaixo pode-se visualizar como o horário completo ficaria gravado e o que AT TIME ZONE retorna.

-- código #10
with C1 as (
SELECT Seq, Horário, Momento,
       cast(Horário as datetime2(0)) as Horário_gravado
   from Evento
)
SELECT Seq, Horário as [Horário completo],
       Horário_gravado,
       Horário_gravado at time zone 'E. South America Standard Time' as [Horário ATTIMEZONE],
       Momento
   from C1;

O resultado é o seguinte:

p006_codigo#10

Nas linhas 7 e 8 do resultado acima percebe-se que AT TIME ZONE não conseguiu restaurar o horário correto, por causa da sobreposição mencionada no início deste capítulo. Os horários que constam nas linhas 7 e 8 foram registrados após o horário de verão, ou seja, no fuso horário UTC-3. Entretanto, por causa da sobreposição mencionada no início deste capítulo, AT TIME ZONE restaurou os horários como se fossem no horário de verão, com fuso horário UTC-2.

4.2. Horário UTC

Esta solução também acarreta em dificuldades na programação, pois o horário real de um dia pode ser armazenado como se fosse no dia seguinte. Por exemplo, ’17/2/2018 22:02′ (UTC-2) ficaria armazenado como ’18/2/2018 00:02′ (UTC), o que pode acarretar em resultados incorretos nas pesquisas em que se considere somente a data, caso a aplicação não trate adequadamente a questão de fuso horário e de horário de verão.

Antes de continuar, reexecute o código #12 (está no item 3.2) para carregar a tabela Batida_Ponto com os horários no fuso horário UTC. A seguir, ao executar o código abaixo

-- código #11
declare @Data date;
set @Data= convert(date, '17/2/2018', 103);
SELECT Chapa, Horário, Evento
   from dbo.Batida_Ponto
   where cast(Horário as date) = @Data;

no resultado não aparece a entrada do turno, ocorrida às 22h02min do dia 17/2/2018, pois foi armazenada como 18/2/2018 às 0h02min.

Uma solução de contorno é transformar a pesquisa de um dia em pesquisa de período, mesmo que seja para pesquisar um único dia. Como período pode-se utilizar

WHERE Horário >= '17/2/2018'
      and Horário < '18/2/2018'

que, ao complementar a parte de fuso horário, tem-se:

WHERE Horário >= '17/2/2018 0:00 -02:00'
      and Horário < '18/2/2018 0:00 -02:00'

A seguir basta converter do fuso horário UTC-2 para UTC. Para essa conversão é necessário utilizar função que trate também horários de verão; no exemplo abaixo foram utilizadas funções do pacote SqlTimeZone.

-- código #13
-- data a pesquisar
declare @Data date;
set @Data= convert(date, '17/2/2018', 103);

-- converte data para horário UTC da época
declare @Data_de datetime2(0), @Data_ate datetime2(0);
set @Data_de= Tzdb.LocalToUtc(@Data, 'America/Sao_Paulo', 1, 1);
set @Data_ate= Tzdb.LocalToUtc(dateadd(day, +1, @Data), 
                               'America/Sao_Paulo', 1, 1);

--
SELECT Chapa, Horário as [Horário UTC registrado],
       [Horário real]= 
                 Tzdb.UtcToLocal(Horário, 'America/Sao_Paulo'),
       Evento
   from dbo.Batida_Ponto
   where Horário >= @Data_de
         and Horário < @Data_ate;

p006_codigo#13

Agora o resultado contemplou a linha referente ao dia 17/2/2018, no horário local. Mas ficou um pouco mais complicado, não?

4.3. Horário TZ

Armazenar o horário com o respectivo fuso horário, usando tipo de dados datetimeoffset, é a única solução que me parece confiável, principalmente para casos em que seja necessário efetuar cálculos de intervalo entre horários.

Caso não seja necessário obter fração de segundos além dos centésimos, pode-se declarar como datetimeoffset(2), o que ocupa então 8 bytes. Ou seja, é o mesmo espaço ocupado pelo tipo de dados datetime, que me parece ser o mais frequentemente utilizado.

4.4. Últimas observações

O objetivo deste artigo foi o de alertar sobre o problema e a atenção necessária em cálculo de tempo decorrido quando as datas limite estão em momentos diferentes do horário (normal ou verão).

A obtenção do horário atual pode se dar tanto no aplicativo quanto no código em T-SQL. Independente da origem, é necessário ficar atento a obtê-lo com o respectivo fuso horário, sempre que essa informação for necessária.

Outro cuidado é com a correção do horário do servidor de onde se obtém o horário atual, pois o relógio do servidor pode estar:

  • no fuso horário UTC-3;
  • no fuso horário UTC;
  • em outro fuso horário

e ainda

  • com ajuste automático de horário de verão;
  • com ajuste manual de horário de verão;
  • sem ajuste de horário de verão.

Desconheço se há algum web service que forneça a hora correta, principalmente por causa do atraso que há no protocolo HTTP. Geralmente é utilizado o protocolo NTP, mas é provável que a porta 123 não esteja liberada no servidor.

5. Referências


PDF
Este artigo também está disponível em arquivo no formato PDF, o que possibilita tanto visualizar online quanto obter cópia (download) para leitura offline e até mesmo impressão. Clique no texto abaixo para acessar.

Horário de verão X datediff()

Publicidade

Deixe um comentário

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

Logo 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 )

Conectando a %s

Este site utiliza o Akismet para reduzir spam. Saiba como seus dados em comentários são processados.

%d blogueiros gostam disto: