HN em destaque: recursos de data/hora de alta precisão no SQLite

 (antonz.org)
2 pontos por GN⁺ 2024-08-16 | 1 comentários | Compartilhar no WhatsApp

Extensão de data/hora de alta precisão

O SQLite oferece funções básicas de data, mas, como havia necessidade de mais recursos, foi criada uma extensão de data/hora de alta precisão chamada sqlean-time. Essa extensão oferece uma API estruturada e vários recursos.

Observação. Adicionar extensões ao SQLite é muito simples. Basta baixar o arquivo e executar um único comando no banco de dados.

Conceitos

Essa extensão usa dois tipos de valor: tempo (Time) e duração (Duration).

  • Tempo (Time): um par composto por segundos e nanossegundos, que representa os segundos desde o tempo zero (0001-01-01 00:00:00 UTC) e os nanossegundos dentro do segundo atual.

    • É possível armazenar o tempo usando uma representação interna, o que permite representar datas de bilhões de anos no passado ou no futuro com precisão de nanossegundos.
    • Também é possível armazenar o tempo como segundos desde a Unix epoch (1970-01-01 00:00:00 UTC), incluindo milissegundos, microssegundos e nanossegundos.
    • O tempo é sempre armazenado e operado em UTC, mas pode ser convertido para um deslocamento de fuso horário específico.

  • Duração (Duration): um número de 64 bits em nanossegundos, capaz de representar valores de até cerca de 290 anos.

Criando valores de tempo

  • Hora atual:

    select time_fmt_iso(time_now());  -- 2024-08-06T21:22:15.431295000Z

  • Data/hora específica:

    select time_fmt_iso(time_date(2011, 11, 18));  -- 2011-11-18T00:00:00Z
select time_fmt_iso(time_date(2011, 11, 18, 15, 56, 35));  -- 2011-11-18T15:56:35Z

Extraindo campos de tempo

Há funções para extrair vários campos de data/hora:

select 'year  = ' || time_get_year(time_now());
select 'month  = ' || time_get_month(time_now());
select 'day   = ' || time_get_day(time_now());

Tempo Unix

Funções para criar valores de tempo a partir do tempo Unix (tempo desde 1970-01-01 UTC):

select time_fmt_iso(time_unix(1321631795));  -- 2011-11-18T15:56:35Z

Funções para converter valores de tempo em tempo Unix:

select time_to_unix(time_now());  -- 1722979335

Comparação de tempo

Funções para comparar valores de tempo:

select time_after(time_now(), time_date(2011, 11, 18));  -- 1
select time_before(time_now(), time_date(2011, 11, 18));  -- 0

Operações com tempo

Funções para adicionar durações a valores de tempo:

select time_fmt_iso(time_add(time_now(), 24*dur_h()));  -- 2024-08-07T21:22:15.431295000Z

Constantes de duração:

  • dur_us() - 1 microssegundo
  • dur_ms() - 1 milissegundo
  • dur_s() - 1 segundo
  • dur_m() - 1 minuto
  • dur_h() - 1 hora

Arredondamento

Funções para arredondar valores de tempo para a precisão do campo especificado:

select 'original  = ' || time_fmt_iso(t.v) from t union all
select 'millennium = ' || time_fmt_iso(time_trunc(t.v, 'millennium')) from t;

Formatação

Funções que retornam strings de tempo em ISO 8601:

select time_fmt_iso(time_date(2011, 11, 18, 15, 56, 35, 666777888), 3*3600);  -- 2011-11-18T18:56:35.666777888+03:00

Constantes de duração

Funções que retornam durações comuns em nanossegundos:

select dur_ns();  -- 1
select dur_us();  -- 1000

Agradecimentos

Essa extensão foi implementada em C e projetada e implementada com base no pacote time da biblioteca padrão do Go (licença BSD 3-Clause).

Instalação e uso

  1. Baixe a versão mais recente
  2. Use na interface de linha de comando do SQLite: 
    sqlite> .load ./time
sqlite> select time_now();

Resumo do GN⁺

  • A extensão sqlean-time adiciona recursos de data/hora de alta precisão ao SQLite, possibilitando várias operações com tempo.
  • Como é possível lidar com tempo e duração em nanossegundos, dá para fazer cálculos de tempo extremamente precisos.
  • Ela oferece vários recursos de formatação e comparação de tempo, facilitando o uso pelos desenvolvedores.
  • Fornece muito mais recursos do que as funções de data padrão do SQLite, sendo útil para projetos que exigem operações temporais complexas.

Leituras relacionadas

1 comentários

 
GN⁺ 2024-08-16
Comentários do Hacker News

  • Pergunta se isso lida com casos especiais de mudanças de fuso horário e descontinuidades no horário local documentados por Jon Skeet

    • Link relacionado: Stack Overflow
    • Link para a explicação em vídeo de 10 minutos do Computerphile: YouTube

  • É melhor não criar bibliotecas de data/hora e criptografia por conta própria

    • Casos extremos intermináveis podem causar problemas
    • Motivo para ser cético ao se deparar com uma nova biblioteca

  • As três diferentes representações/escopos de tempo são interessantes

    • Fica a curiosidade sobre casos de uso que exijam precisão de nanossegundos ao longo de períodos de bilhões de anos
    • É confuso que com precisão de nanossegundos o intervalo seja de apenas ±290 anos

  • É importante esclarecer se são usados inteiros com sinal

    • Lendo a documentação, não fica claro se podem ser inteiros com sinal ou não
    • Se forem inteiros com sinal, pode haver várias sequências de bits representando a mesma data e hora

  • Seria bom se o SQLite3 tivesse um sistema de tipos extensível

  • É avaliado como algo muito legal, ao mencionar um recurso importante que falta no SQLite

  • Argumenta que o banco de dados deveria rastrear unidades

    • Por exemplo, deveria ser possível especificar que uma coluna de tempo representa segundos em float64
    • O banco de dados deveria conseguir converter "2h" em 7200.0 segundos e comparar isso durante um table scan
    • No passado, escreveu um banco de dados SQL de propósito específico que fazia esse tipo de tratamento de unidades, mas nunca mais viu algo assim desde então
    • Isso deveria funcionar não só para tempo, mas também para massa, volume, informação, temperatura e todas as outras unidades
    • O banco de dados poderia ser ensinado a rejeitar operações matematicamente sem sentido para detectar erros matemáticos mais cedo

  • Pergunta qual é mais útil: a representação em nanossegundos ou anos fora do alcance nano

    • Como não faz ciência "exata", o valor dos nanossegundos é limitado
    • Parece mais comum precisar representar datas históricas

  • Sugere usar timestamps Unix no estilo do golang em nanossegundos, como int64 com sinal

    • Talvez isso não cubra milhões de anos com precisão de nanossegundos, mas fica a dúvida se isso realmente é necessário

  • Argumenta que não se deve usar a expressão "segundos desde a epoch" a menos que ela signifique exatamente isso

    • Exemplo de consulta: select time_sub(time_date(2011, 11, 19), time_date(1311, 11, 18));