Solucionar problemas de fuso horário, GMT e UTC usando o banco de dados T-SQL Toolbox

Solucionar problemas de fuso horário, GMT e UTC usando o banco de dados T-SQL Toolbox

Testing the CONVERT_GETDATE_VALUE_FROM_ONE_TIMEZONE_TO_UTC user-defined function.
cupom com desconto - o melhor site de cupom de desconto cupomcomdesconto.com.br


Introdução

Em breve, os aplicativos e o software de banco de dados que criamos lidarão com valores de data e hora. O banco de dados T-SQL Toolbox – um download gratuito – pode ajudar a resolver cálculos complexos com esses valores.

Eu moro nos EUA, assim como muitos de meus clientes. Devemos lidar com o horário de verão, o que significa que, em datas específicas, o horário local em um fuso horário específico pode avançar uma hora e, em outras datas, o horário local pode voltar uma hora. Essas mudanças acontecem em um agendamento definido. Uma mudança para frente sempre segue uma mudança para trás e uma mudança para trás sempre segue uma mudança para frente. Eles nunca se acumulam. Lidar com tudo isso na camada de banco de dados pode se tornar realmente complexo, e o T-SQL Toolbox pode economizar muito esforço com isso. Em uma conferência, ouvi um palestrante explicar que deveríamos usar o horário médio de Greenwich, ou GMT, para valores de data e hora do banco de dados para evitar esse problema. A GMT nunca muda para o horário de verão e podemos calcular facilmente os valores da hora local com base nos dados da GMT. Embora o SQL Server ofereça várias funções internas de data e hora, essas funções não tratam diretamente as conversões entre os valores do fuso horário local e o GMT, e certamente não tratam as conversões de horário de verão. Felizmente, encontrei o T-SQL Toolbox como uma maneira de evitar a criação das funções necessárias para tudo isso. Disponível aqui no CodePlex Archive e aqui no GitLab, o T-SQL Toolbox fornece UDFs (funções definidas pelo usuário) do SQL Server que convertem valores de data / hora entre fusos horários, incluindo GMT. O T-SQL Toolbox também fornece UDFs que calculam o início ou o fim

  • dia

  • semana

  • mês

  • trimestre

  • ano

Valores DateTime para um determinado valor de data / hora.

Baixar e instalar

Após o download:

Arquivo tsqltoolbox.zip baixado em um diretório host. Este arquivo possui um script para criar o banco de dados T-SQL Toolbox.

e extração de arquivos, como mostrado abaixo:

Subdiretórios TSqlToolbox.zip extraídos.

Como visto na captura de tela a seguir, faça uma busca detalhada no diretório sourceCode:

Faça uma busca detalhada no diretório sourceCode, para encontrar o arquivo sourceCode.zip.

Em seguida, faça uma busca detalhada no arquivo sourceCode.zip. Copie o arquivo TSqlToolbox.sql

Copie o arquivo TSqlToolbox.sql do arquivo ZIP do host.

Coloque o arquivo TSqlToolbox.sql em um diretório. Este script SQL criará o banco de dados T-SQL Toolbox.

Executaremos o arquivo TSqlToolbox.sql para instalar o banco de dados T-SQL Toolbox no SQL Server. Instalei-o em um ambiente do SQL Server 2014 Standard Edition em um PC com Windows 10.

O arquivo TSqlToolbox.sql possui um script CREATE DATABASE e conjuntos de scripts CREATE TABLE e CREATE FUNCTION. Abra o TSqlToolbox.sql em uma janela de consulta do SQL Server

As primeiras linhas do arquivo TSqlToolbox.sql. Este script criará o banco de dados T-SQL Toolbox.

Isso criará o banco de dados completo do TSqlToolbox, suas tabelas, linhas de dados da tabela, funções e restrições de chave primária / externa. Na captura de tela acima, o servidor de banco de dados FRANK-PC hospeda o TSqlToolbox. Todo banco de dados hospedado pelo servidor de banco de dados FRANK-PC pode usar as funções TSqlToolbox.

O Pesquisador de Objetos mostra as tabelas e funções do banco de dados T-SQL Toolbox.

Sob o capô

O banco de dados T-SQL Toolbox usa dados nas tabelas a seguir para seus cálculos.

  1. DateTimeUtil.Timezone

  2. DateTimeUtil.TimezoneAdjustmentRule

o Fuso horário A tabela possui uma linha para cada fuso horário definido no banco de dados.

A rede TimeZoneInfo A classe pode servir como fonte de dados principal para esta tabela porque, como visto aqui na documentação da Microsoft, esta classe tem as seguintes propriedades:

  • Eu iria

  • StandardName

  • Nome em Exibição

  • DaylightName

  • SupportsDaylightSavingTime

  • BaseUtcOffset

A caixa de ferramentas T-SQL TimezoneAdjustmentRule A tabela possui linhas que mostram alterações históricas de metadados para cada alteração de fuso horário, os atuais metadados definidos para cada fuso horário e planejadas alterações futuras para fusos horários específicos.

A rede TimeZoneInfo.AdjustmentRule A classe pode servir como fonte de dados primária para esta tabela porque, como visto aqui na documentação da Microsoft, suas propriedades e métodos refletem muitas das colunas desta tabela. Podemos ver isso claramente quando pesquisamos as propriedades DateEnd e DaylightTransitionStart dessa classe, por exemplo.

Usando o T-SQL Toolbox

Usaremos um banco de dados personalizado para trabalhar com o TSqlToolbox. Execute este script em uma janela de consulta do SQL Server:

Se necessário, ele primeiro cria um subdiretório, TSQL_TOOLBOX_DEMO na raiz e, em seguida, cria um novo banco de dados chamado TSQL_TOOLBOX_DEMO. Ele coloca os arquivos LDF e MDF do componente do banco de dados T-SQL Toolbox no diretório TSQL_TOOLBOX_DEMO. Por fim, cria o CONVERT_GETDATE_VALUE_FROM_ONE_TIMEZONE_TO_UTC procedimento armazenado, como mostrado.

Na instrução SELECT do procedimento armazenado, a terceira expressão nas linhas 25 e 26 chama o TSqlToolbox DateTimeUtil.UDF_ConvertLocalToUtcByTimezoneId função. Como muitas outras funções do TSqlToolbox, essa função é responsável pelo horário de verão por meio de chamadas para o TSqlToolbox.DateTimeUtil.TimezoneAdjustmentRule mesa.

A chamada nas linhas 25 e 26 coloca o nome do banco de dados TSqlToolbox na frente do nome completo da função. Em seguida, adiciona os parâmetros @timezoneID e GETDATE (). Observe que as funções possuem nomes de duas partes. “DateTimeUtil” serve como o prefixo de todos os nomes de funções do TSqlToolbox e um ponto separa o segundo nome de função de identificação. Se um nome de função TSqlToolbox não incluísse o prefixo “DateTimeUtil”, precisaríamos substituir o nome do esquema padrão do banco de dados TSqlToolbox. Geralmente usamos o dbo para isso; “Dbo” significa “proprietário do banco de dados”. No entanto, ambientes de banco de dados diferentes podem lidar com essa nomeação de maneira diferente; portanto, verifique com um administrador de banco de dados detalhes detalhados.

Uma função definida pelo usuário que mostra como chamar as funções de banco de dados do T-SQL Toolbox.

Na linha 5, o procedimento armazenado de demonstração possui código de teste para o próprio procedimento armazenado. O código de teste retorna esta linha:

Testando a função definida pelo usuário CONVERT_GETDATE_VALUE_FROM_ONE_TIMEZONE_TO_UTC.

Na linha 17, ele mapeia o parâmetro @timezoneID para o nome do fuso horário. A instrução Line 23 SELECT cria o conjunto de resultados do procedimento armazenado concluído. Isso mostra que, com uma chamada para uma função TSqlToolbox, podemos mapear facilmente um valor local de data e hora para um valor GMT. Nesse caso, tentamos uma chamada para GETDATE (). O TSqlToolbox é responsável por quaisquer efeitos locais do horário de verão.

Quando olhamos para as funções do banco de dados T-SQL Toolbox, podemos ver que ambos aproveitam as funções nativas do SQL Server e outras funções do TSqlToolbox. Por exemplo, a função UDF_GetStartOfDay primeiro converte o parâmetro @ReferenceDate – originalmente um tipo de dados DATETIME2 – em um tipo de dados DATE. Isso retira os dados de tempo do valor @ReferenceDate. Em seguida, ele converte esse valor intermediário novamente em um tipo de dados DATETIME2. Essa conversão restaurou os dados de horário, mas zerou o horário para o início do dia (SOD).

o [DateTimeUtil].[UDF_GetStartOfDay] Função TSqlToolbox.

A próxima captura de tela mostra as conversões equivalentes.

Cálculos para a caixa de ferramentas T-SQL [DateTimeUtil].[UDF_GetStartOfDay] função.

Como visto abaixo, a função TSqlToolbox UDF_ConvertLocalToLocalByTimezoneIdentifier chama a TSqlToolbox: UDF_ConvertUtcToLocalByTimezoneIdentifier e UDF_ConvertLocalToUtcByTimezoneIdentifier funcionam para converter um fuso horário DATETIME2 em outro parâmetro do fuso horário. Os dois primeiros parâmetros têm valores da coluna [TSqlToolbox].[DateTimeUtil].[Timezone].Identificador.

o [DateTimeUtil].[UDF_ConvertLocalToLocalByTimezoneIdentifier] Função TSqlToolbox.

Estender TSqlToolbox

Como explicado anteriormente, o banco de dados T-SQL Toolbox oferece funções para calcular o início ou o fim do dia, semana, etc. Valores de DateTime para um determinado valor DATETIME2. Podemos facilmente estender essas funções. Por exemplo, criei e adicionei a função UDF_GetEndOfDecade, vista aqui, ao meu banco de dados TSqlToolbox local. Ele calcula o valor DATETIME2 do final da década para um determinado parâmetro DATETIME2.

Esta função calcula primeiro o valor de final de ano (EOY) para o parâmetro de entrada. Em seguida, ele analisa esse valor EOY em valores separados de mês, dia e ano. O cálculo da variável @YearOffset usa a função% (mod) para calcular o número de anos restantes na década do valor do parâmetro ano. Por fim, a instrução primeiro reúne um valor de data final de todos os valores do componente e, em seguida, usa a função UDF_GetEndOfDay para converter esse valor em um valor de retorno DATETIME2.

Atualizar dados internos da caixa de ferramentas T-SQL

As funções do banco de dados TSqlToolbox dependem dos dados em suas tabelas Timezone e TimezoneAdjustmentRule para seus cálculos. Obviamente, esses dados não são alterados nas próprias tabelas. Matt Johnson-Pint, da Microsoft, foca na engenharia de fuso horário do Windows. Em seu blog, ele discutiu como os fusos horários mudam constantemente. Nesse segmento de estouro de pilha, ele explicou que o Windows armazena informações de fuso horário no registro. Ele forneceu mais detalhes sobre como isso funciona neste encadeamento de estouro de pilha. Embora o banco de dados T-SQL Toolbox tenha um valor enorme, precisamos lembrar que seus dados principais podem se tornar obsoletos sem atualizações. O processo de atualização do Microsoft Windows atualiza as informações de fuso horário do registro; portanto, o registro de um dispositivo totalmente atualizado deve ter as informações mais recentes sobre o fuso horário. Criei esse aplicativo de desktop do Windows C-Sharp para consultar o registro e retornar os dados das classes .Net TimeZoneInfo e TimeZoneInfo.AdjustmentRule. Essas classes consultam os valores do fuso horário do registro, que podemos usar para atualizar o T-SQL Toolbox. O aplicativo usa um símbolo de tubo “|” delimitar os valores da coluna nas linhas. Também construí esta versão do VB.net, que funciona da mesma maneira. Hospedei as versões C-Sharp e VB.net na minha página do GitHub. Ambos incluem soluções completas do Visual Studio 2015 e arquivos executáveis ​​concluídos.

Aplicativos do Visual Studio .Net que consultam o registro do Windows para mostrar informações de fuso horário.

Para criar tabelas do SQL Server com os dados de saída do aplicativo, primeiro atualize o Windows na máquina de desenvolvimento. Isso aplicará todas as atualizações de fuso horário ao registro. Em seguida, execute o aplicativo e copie as linhas das caixas de texto do formulário. Use Ctrl-C para copiar porque o formulário usa caixas de rich text. Coloque as linhas em novos arquivos de texto. Trunque as tabelas do banco de dados TSqlToolbox que você atualizará para que eles tenham dados completamente atualizados. Use o Assistente para Importação e Exportação do SQL Server para importar os valores para as tabelas do banco de dados T-SQL Toolbox. Se esse assistente falhar devido a problemas com os dados, reformate os dados para uma instrução INSERT do SQL Server e continue com as instruções INSERT em uma janela de consulta. Para criar os relacionamentos de tabela pai / filho, adicione colunas de ID de valor inteiro às tabelas como chaves primárias e estrangeiras.

Conclusão

Como vimos, o T-SQL Toolbox resolve problemas complicados e retorna um valor enorme. Ainda melhor, podemos estendê-lo facilmente e atualizar seus dados para cobrir uma gama crescente de regras e requisitos de negócios.

Frank Solomon
Últimas mensagens de Frank Solomon (ver todos)

cupom com desconto - o melhor site de cupom de desconto cupomcomdesconto.com.br
Leia Também  Mudando de idéia - SQLBlog