Usando datas/horas de alta precisão no SQLite: sqlean-time

 (antonz.org)
2 pontos por GN⁺ 2024-08-16 | 1 comentários | Compartilhar no WhatsApp
  • Quando as funções de data padrão do SQLite não são suficientes, sqlean-time adiciona, como extensão, tipos Time e Duration com precisão de nanossegundos e funções de data e hora
  • Um valor Time é composto pelos segundos desde 0001-01-01 00:00:00 UTC e pelos nanossegundos dentro do segundo atual; ao ser armazenado como um BLOB de 13 bytes, pode lidar com intervalos de bilhões de anos no passado e no futuro
  • Também é possível armazenar como NUMBER de 64 bits com base no Unix epoch, mas quanto menor a unidade, menor o intervalo: em nanossegundos, só é possível representar de 1678 a 2262
  • A API inclui criação, extração de campos, conversão de Unix time, comparação, aritmética, truncamento e arredondamento, formatação e parsing ISO 8601; os valores são sempre armazenados e operados em UTC
  • Os cálculos de calendário pressupõem o calendário gregoriano e não lidam com segundos bissextos; portanto, para cálculos de dias, meses e anos, deve-se usar time_add_date() em vez de time_add()

Modelo de tempo do sqlean-time

  • sqlean-time é uma extensão que adiciona processamento de data e hora de alta precisão ao SQLite
  • Extensões do SQLite podem ser adicionadas baixando um arquivo e executando um comando no banco de dados
  • A extensão opera em torno de dois tipos de valores
    • Time: um instante específico
    • Duration: uma duração

Representação de Time e intervalo de armazenamento

  • Time é composto por um par (seconds, nanoseconds)
    • seconds: um inteiro de 64 bits que representa os segundos desde o zero time, 0001-01-01 00:00:00 UTC
    • nanoseconds: o valor em nanossegundos dentro do segundo atual, no intervalo 0-999999999
  • Se for necessária a máxima flexibilidade, valores Time podem ser armazenados em sua representação interna como um BLOB de 13 bytes
    • Esse método representa datas de bilhões de anos no passado e no futuro com precisão de nanossegundos
  • Também há suporte ao armazenamento, como NUMBER inteiro de 64 bits, dos segundos, milissegundos, microssegundos e nanossegundos desde o Unix epoch, 1970-01-01 00:00:00 UTC
    • Segundos: representa bilhões de anos no passado e no futuro com precisão de segundos
    • Milissegundos: representa 292 milhões de anos antes e depois de 1970 com precisão de milissegundos
    • Microssegundos: representa de -290307 até 294246
    • Nanossegundos: representa de 1678 até 2262
  • Time é sempre armazenado e operado em UTC
    • É possível convertê-lo de/para offsets de fuso horário específicos
  • Os cálculos de calendário sempre assumem o calendário gregoriano
    • Segundos bissextos não são usados

Duration e criação de valores

  • Duration é um inteiro de 64 bits em nanossegundos
    • Pode representar durações de até cerca de 290 anos
    • Pode ser armazenado como NUMBER
  • O instante atual pode ser criado com time_now()
    • Exemplo: time_fmt_iso(time_now()) retorna uma string ISO como 2024-08-06T21:22:15.431295000Z
  • Uma data e hora específicas são criadas com time_date()
    • Se apenas a data for especificada, ela será meia-noite UTC
    • É possível especificar também hora, minuto, segundo e nanossegundos
    • Se um offset de fuso horário for passado, ele será convertido para um instante em UTC

Extração de campos de data e hora

  • As funções de extração de campos individuais retornam ano, mês, dia, hora, minuto, segundo, nanossegundos, dia da semana, dia do ano, ano ISO e semana ISO
    • Exemplos: time_get_year(), time_get_month(), time_get_day(), time_get_hour(), time_get_minute(), time_get_second(), time_get_nano()
  • A função genérica time_get() extrai valores usando uma string com o nome do campo
    • Exemplos com suporte: millennium, century, decade, year, quarter, month, day
    • Exemplos de unidades de tempo: hour, minute, second, milli, micro, nano
    • Exemplos relacionados a ISO e calendário: isoyear, isoweek, isodow, yearday, weekday
    • Valores do Unix epoch podem ser obtidos com epoch

Conversão de Unix time

  • São fornecidas funções para criar valores Time a partir de Unix time
    • time_unix(seconds)
    • time_unix(seconds, nanoseconds)
    • time_milli(milliseconds)
    • time_micro(microseconds)
    • time_nano(nanoseconds)
  • Também há funções para converter valores Time de volta para Unix time
    • time_to_unix()
    • time_to_milli()
    • time_to_micro()
    • time_to_nano()
  • Sistemas operacionais Unix-like frequentemente registram o tempo como um valor de segundos de 32 bits, mas time_to_unix() retorna um valor de 64 bits
    • É válido em um intervalo de bilhões de anos no passado e no futuro
    • time_to_milli() representa até 292 milhões de anos antes e depois de 1970
    • time_to_micro() representa de -290307 até 294246
    • time_to_nano() representa de 1678 até 2262

Comparação e aritmética

  • As funções de comparação de tempo determinam a ordem de dois valores Time
    • time_after(): retorna se o primeiro horário é posterior ao segundo
    • time_before(): retorna se o primeiro horário é anterior ao segundo
    • time_compare(): retorna 1 se for posterior, -1 se for anterior e 0 se for igual
    • time_equal(): retorna se os dois valores representam o mesmo instante
  • time_add() soma uma Duration a um valor Time
    • É possível subtrair usando uma Duration negativa
    • Pode ser usado com constantes de Duration como dur_us(), dur_ms(), dur_s(), dur_m(), dur_h()
  • Ao somar dias, meses e anos, deve-se usar time_add_date(), não time_add()
    • time_add_date() soma anos, meses e dias, e permite subtrair com valores negativos
  • time_sub() retorna a duração entre dois valores Time em nanossegundos
  • time_since() retorna, em nanossegundos, o tempo decorrido desde o instante especificado
  • time_until() retorna, em nanossegundos, a duração restante até o instante especificado

Truncamento e arredondamento

  • time_trunc() arredonda um valor Time para baixo até a precisão de campo especificada
    • Exemplos com suporte: millennium, century, decade, year, quarter, month, week, day, hour, minute, second, milli, micro
    • Por exemplo, truncar 2011-11-18T15:56:35.666777888Z para hour resulta em 2011-11-18T15:00:00Z
  • Também é possível truncar para baixo para múltiplos de uma Duration especificada
    • Exemplos: 12*dur_h(), dur_h(), 30*dur_m(), dur_m(), 30*dur_s(), dur_s()
  • time_round() arredonda para o múltiplo mais próximo de uma Duration especificada
    • Exemplo: arredondar 2011-11-18T15:56:35.666777888Z para dur_h() resulta em 2011-11-18T16:00:00Z
    • Arredondar o mesmo valor para dur_s() resulta em 2011-11-18T15:56:36Z

Formatação e parsing

  • time_fmt_iso() retorna um valor Time como uma string ISO 8601
    • Pode receber opcionalmente um offset de fuso horário para converter para esse offset antes de formatar
    • Valores com nanossegundos são representados como 2011-11-18T15:56:35.666777888Z
    • Ao especificar um offset, são representados como 2011-11-18T18:56:35.666777888+03:00
  • time_fmt_datetime(), time_fmt_date() e time_fmt_time() retornam, respectivamente, strings de datetime, date e time
    • Podem receber opcionalmente um offset de fuso horário
  • time_parse() faz o parsing de uma string formatada para um valor Time
    • String ISO 8601 com nanossegundos e fuso horário
    • String ISO 8601 com nanossegundos e UTC Z
    • String ISO 8601 com fuso horário
    • String ISO 8601 em UTC
    • Data e hora em UTC no formato YYYY-MM-DD HH:MM:SS
    • Data em UTC no formato YYYY-MM-DD
    • Hora em UTC no formato HH:MM:SS
  • Os layouts suportados por time_parse() são um conjunto limitado

Constantes de Duration

  • São fornecidas funções que retornam durações comuns em nanossegundos
    • dur_ns() → 1
    • dur_us() → 1000
    • dur_ms() → 1000000
    • dur_s() → 1000000000
    • dur_m() → 60000000000
    • dur_h() → 3600000000000

Base de implementação e instalação

  • A extensão foi implementada em C, mas seu design e implementação se baseiam fortemente no pacote time da biblioteca padrão do Go
    • Esse pacote usa a BSD 3-Clause License
  • A instalação consiste em baixar a latest release e carregar a extensão no SQLite CLI
    • Exemplo: .load ./time
    • Após o carregamento, é possível usar consultas como select time_now();

Leituras relacionadas

1 comentários

 
GN⁺ 2024-08-16
Opiniões no Hacker News

  • Fico curioso se também lida com casos especiais como mudanças de fuso horário e descontinuidades na hora local, que Jon Skeet compilou de forma famosa
    https://stackoverflow.com/questions/6841333/why-is-subtracti...
    A Computerphile também explica muito bem em um vídeo de 10 minutos
    https://www.youtube.com/watch?v=-5wpm-gesOY
    Há muito tempo aprendi a não criar minhas próprias bibliotecas de data/hora ou de criptografia. Há uma infinidade de casos de borda que podem dar muito errado, então fico cético quando vejo uma biblioteca nova assim

    • Esta biblioteca não trata de forma alguma o conceito de hora local. Tudo é horário baseado em UTC, e o usuário pode fornecer um offset de fuso horário, mas a parte difícil de calcular esse offset fica a cargo de quem chama
      Acho que a documentação poderia ser um pouco mais clara. O autor fala em “time zones”, mas a biblioteca na verdade só lida com offsets de fuso horário. Um fuso horário é algo como America/New_York; um offset de fuso horário é a diferença em relação ao UTC. Nova York hoje está em -14400 segundos, mas daqui a alguns meses estará em -18000 segundos por causa da mudança do horário de verão

  • As três representações/tamanhos de tempo diferentes são interessantes. Por exemplo, não sei qual seria o caso de uso que precisa de precisão em nanossegundos numa faixa de bilhões de anos
    O que confunde mais é que a granularidade do tempo é extremamente fina, mas, em durações, a precisão em nanossegundos só cobre uma faixa de ±290 anos

    • Uma vez que se decide usar precisão em nanossegundos, uma representação de 64 bits só comporta 584 anos, e isso não é suficiente. São necessários pelo menos mais 2 bits para representar 2024
      Mas, se você vai adicionar 2 bits, não há muito motivo para não adicionar 16 ou 32 bits. Assim dá para cobrir desde quem calcula o tempo que a luz leva para percorrer 30 cm até quem calcula a idade do universo
      Imagino que a decisão de design tenha seguido algo nessa linha :)
      Claro que é difícil oferecer precisão abaixo de segundo sem suporte a segundos intercalares, e também é meio ambíguo que sentido faria dar suporte a segundos intercalares antes da civilização humana
    • Essa abordagem funcionou muito bem para mim e para milhares de outros desenvolvedores Go. Por isso escolhi esse caminho

  • Um desvio relacionado: bancos de dados deveriam rastrear unidades. Se houver uma coluna de tempo, deveria ser possível declará-la, por exemplo, como uma duração em segundos float64
    Então você escreveria SELECT * FROM my_table WHERE duration_s >= 2h, e o banco de dados converteria automaticamente “2h” para 7200.0 segundos e compararia unidades iguais durante o scan da tabela
    Alguns anos atrás criei um banco de dados SQL de propósito específico com esse tratamento nativo de unidades, mas não vi isso antes nem depois, e parece uma lacuna no ecossistema de UI
    Isso não precisa se limitar ao tempo. Deveria ser possível lidar com uma lista completa de unidades: massa, volume, quantidade de informação, temperatura etc. O banco de dados também poderia rejeitar expressões matematicamente sem sentido, como SELECT 2h + 15kg -- type error!
    Isso ajudaria muito a detectar erros de análise cedo

  • Acho importante deixar explícito se usa inteiros com sinal ou não. Lendo a documentação, parece que são com sinal, mas talvez não sejam
    Se forem inteiros com sinal, pode haver várias strings de bits representando a mesma data e hora, e isso não é bom

    • Definitivamente têm sinal. Está escrito: “para subtrair, use uma duração negativa”
      Mas o padrão de bits é uma questão interna da biblioteca. Se você conseguir encontrar um bug no código, claro, aponte; e, se possível, proponha uma correção
    • Como seria possível haver “várias strings de bits representando a mesma data e hora”?

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

    • Falando como alguém que contribuiu um pouco com o PostgreSQL: não, isso não deve ser feito!!!!
      Um sistema de tipos extensível é péssimo para o desempenho do usuário final do banco de dados. Com isso, nada pode ser tratado por atalhos no parsing e na otimização de consultas. É preciso consultar constantemente os catálogos do sistema de consultas para verificar o tipo de cada operando, encontrar a implementação correta do operador, encontrar a família/classe correta de operadores de índice etc.
      A entrada e saída de valores também passa por funções armazenadas no catálogo do sistema. Nem mesmo select 1 pode ser respondido sem olhar o catálogo do sistema
      Deve haver um conjunto adequado de tipos embutidos e formas de composição como structs/JSON. A maioria dos bancos de dados fora o PostgreSQL funciona assim, e acredito firmemente que esse é o caminho correto

  • Uma pergunta meio preguiçosa no estilo Ask HN: pela experiência de vocês, o que é mais útil ou valioso? Representação em nanossegundos ou representação de anos fora da faixa em nanossegundos, como 1678–2200?
    Não faço trabalho científico sério, então o valor dos nanossegundos me parece limitado a experimentos muito engenhosos ou ao rastreamento de transações financeiras, onde a faixa seria mais estreita
    Por outro lado, a capacidade de representar datas históricas parece algo que seria necessário com mais frequência. O que acham?

    • Datas históricas certamente são mais importantes
      Só de reduzir a precisão para 10 nanossegundos, já se obtém uma faixa suficiente na prática
    • É parecido com perguntar o que é mais útil, um martelo ou uma chave de fenda. Depende do trabalho

  • Fico pensando por que não usar, no estilo Go, um timestamp Unix como int64 com sinal em nanossegundos. Não cobriria milhões de anos com precisão em nanossegundos, mas isso é realmente necessário?

    • Com essa precisão e tamanho, só é possível cobrir de 1678 a 2262, o que limita bastante a capacidade de representar datas e horas históricas
    • Armazenar timestamps Unix em nanossegundos não é o estilo Go, mas dá para fazer isso com esta extensão
      select time_to_nano(time_now());
      -- 1722979335431295000

  • Gostaria que expressões como “segundos desde a epoch” fossem usadas apenas quando significassem exatamente isso
    Fico curioso para saber o que select time_sub(time_date(2011, 11, 19), time_date(1311, 11, 18)); retornaria

    • Por que você deseja isso?
      Consigo imaginar algumas razões plausíveis, mas a única coisa realmente importante é “qual epoch?”. Em sistemas baseados em UNIX, ou em sistemas que tentam imitar esse comportamento, isso é bem definido. Mas, como você não disse qual é a reclamação, fica difícil rebater ou justificar por que está como está
      time_date(1311, 11, 18) não é definido para a epoch usada pela maioria dos sistemas de computador, então qualquer resultado é possível. Pode ser MAX_INT, MIN_INT, 0, um valor plausível mas que não reflete reformas do calendário, um valor convertido para outra epoch e calculado como número exato de segundos etc. Antes de GMT/UTC, tudo era hora local, então também se poderia argumentar que não há uma epoch válida
      Claro, dá para argumentar dos dois lados sobre se valores negativos deveriam ser suportados. Exatamente 24 horas antes de 1970-1-1 0:00:00 UTC você poderia esperar -86400, mas “since” sugere fortemente apenas valores positivos
      Outras pessoas podem ter epochs completamente diferentes por outros motivos, e tudo bem se todos concordarem dentro do domínio usado
      Ou havia algum outro motivo para a objeção?
    • Está escrito que “se o resultado exceder o valor máximo que pode ser armazenado em Duration, a duração máxima será retornada”