Escribir fecha/hora de alta precisión en SQLite: sqlean-time

 (antonz.org)
2 puntos por GN⁺ 2024-08-16 | 1 comentarios | Compartir por WhatsApp
  • Cuando las funciones de fecha de SQLite no son suficientes, sqlean-time agrega como extensión tipos Time y Duration con precisión de nanosegundos y funciones de fecha y hora
  • Un valor Time se compone de los segundos transcurridos desde 0001-01-01 00:00:00 UTC y los nanosegundos dentro del segundo actual; al guardarse como un BLOB de 13 bytes, puede cubrir decenas de miles de millones de años hacia el pasado y el futuro
  • También puede almacenarse como un NUMBER de 64 bits basado en Unix epoch, pero mientras más pequeña sea la unidad, menor será el rango; en nanosegundos solo puede representarse desde 1678 hasta 2262
  • La API incluye creación, extracción de campos, conversión a Unix time, comparación, aritmética, truncado y redondeo, además de formateo y parsing en ISO 8601; los valores siempre se almacenan y operan en UTC
  • Los cálculos de calendario asumen el calendario gregoriano y no manejan segundos intercalares, por lo que para sumar días, meses o años debe usarse time_add_date() en lugar de time_add()

El modelo de tiempo de sqlean-time

  • sqlean-time es una extensión que agrega manejo de fecha y hora de alta precisión a SQLite
  • Las extensiones de SQLite pueden añadirse descargando un archivo y ejecutando un solo comando en la base de datos
  • La extensión gira en torno a dos tipos de valores
    • Time: un instante específico
    • Duration: un período de tiempo

Representación de Time y rango de almacenamiento

  • Time se compone de un par (seconds, nanoseconds)
    • seconds: un entero de 64 bits que representa los segundos desde el zero time 0001-01-01 00:00:00 UTC
    • nanoseconds: el valor de nanosegundos dentro del segundo actual, con rango 0-999999999
  • Si se necesita la máxima flexibilidad, el valor Time puede guardarse como su representación interna: un BLOB de 13 bytes
    • Este método representa fechas con precisión de nanosegundos a lo largo de decenas de miles de millones de años en el pasado y el futuro
  • También se admite almacenar como NUMBER entero de 64 bits los segundos, milisegundos, microsegundos o nanosegundos desde Unix epoch 1970-01-01 00:00:00 UTC
    • Segundos: representa miles de millones de años hacia el pasado y el futuro con precisión de segundos
    • Milisegundos: representa 292 millones de años alrededor de 1970 con precisión de milisegundos
    • Microsegundos: representa desde el año -290307 hasta 294246
    • Nanosegundos: representa desde 1678 hasta 2262
  • Time siempre se almacena y se opera en UTC
    • Es posible convertirlo con un offset de zona horaria específico
  • Los cálculos de calendario siempre asumen el calendario gregoriano
    • No se usan segundos intercalares

Duration y creación de valores

  • Duration es un entero de 64 bits en nanosegundos
    • Puede representar períodos de hasta unos 290 años
    • Puede almacenarse como NUMBER
  • La hora actual puede crearse con time_now()
    • Ejemplo: time_fmt_iso(time_now()) devuelve una cadena ISO como 2024-08-06T21:22:15.431295000Z
  • Una fecha y hora específica se crea con time_date()
    • Si solo se indica la fecha, será la medianoche en UTC
    • También pueden especificarse hora, minuto, segundo y nanosegundos
    • Si se pasa un offset de zona horaria, se convierte al instante UTC

Extracción de campos de fecha y hora

  • Las funciones de extracción de campos individuales devuelven año, mes, día, hora, minuto, segundo, nanosegundo, día de la semana, día del año, año ISO y semana ISO
    • Ejemplo: time_get_year(), time_get_month(), time_get_day(), time_get_hour(), time_get_minute(), time_get_second(), time_get_nano()
  • La función genérica time_get() extrae valores usando una cadena con el nombre del campo
    • Ejemplos admitidos: millennium, century, decade, year, quarter, month, day
    • Ejemplos de unidades de tiempo: hour, minute, second, milli, micro, nano
    • Ejemplos relacionados con ISO y calendario: isoyear, isoweek, isodow, yearday, weekday
    • El valor de Unix epoch puede obtenerse con epoch

Conversión de Unix time

  • Se proporcionan funciones para crear un valor Time a partir de Unix time
    • time_unix(seconds)
    • time_unix(seconds, nanoseconds)
    • time_milli(milliseconds)
    • time_micro(microseconds)
    • time_nano(nanoseconds)
  • También hay funciones para convertir un valor Time de vuelta a Unix time
    • time_to_unix()
    • time_to_milli()
    • time_to_micro()
    • time_to_nano()
  • En los sistemas tipo Unix, a menudo el tiempo se registra como un valor de segundos de 32 bits, pero time_to_unix() devuelve un valor de 64 bits
    • Es válido en un rango de miles de millones de años hacia el pasado y el futuro
    • time_to_milli() representa hasta 292 millones de años alrededor de 1970
    • time_to_micro() representa desde el año -290307 hasta 294246
    • time_to_nano() representa desde 1678 hasta 2262

Comparación y aritmética

  • Las funciones de comparación temporal determinan el orden entre dos valores Time
    • time_after(): devuelve si el primer instante es posterior al segundo
    • time_before(): devuelve si el primer instante es anterior al segundo
    • time_compare(): devuelve 1 si es posterior, -1 si es anterior y 0 si son iguales
    • time_equal(): devuelve si ambos valores representan el mismo instante
  • time_add() suma un Duration a un valor Time
    • Si se usa un Duration negativo, también puede restarse
    • Puede usarse junto con constantes Duration como dur_us(), dur_ms(), dur_s(), dur_m(), dur_h()
  • Para sumar días, meses o años, debe usarse time_add_date() y no time_add()
    • time_add_date() suma años, meses y días, y permite restar usando valores negativos
  • time_sub() devuelve el período entre dos valores Time en nanosegundos
  • time_since() devuelve en nanosegundos el tiempo transcurrido desde el instante indicado
  • time_until() devuelve en nanosegundos el tiempo restante hasta el instante indicado

Truncado y redondeo

  • time_trunc() recorta un valor Time hasta la precisión del campo indicado
    • Ejemplos admitidos: millennium, century, decade, year, quarter, month, week, day, hour, minute, second, milli, micro
    • Por ejemplo, truncar 2011-11-18T15:56:35.666777888Z a hour da 2011-11-18T15:00:00Z
  • También puede recortarse a múltiplos de un Duration específico
    • Ejemplos: 12*dur_h(), dur_h(), 30*dur_m(), dur_m(), 30*dur_s(), dur_s()
  • time_round() redondea al múltiplo más cercano del Duration indicado
    • Ejemplo: redondear 2011-11-18T15:56:35.666777888Z con dur_h() da 2011-11-18T16:00:00Z
    • Redondear ese mismo valor con dur_s() da 2011-11-18T15:56:36Z

Formateo y parsing

  • time_fmt_iso() devuelve un valor Time como cadena ISO 8601
    • Opcionalmente puede recibir un offset de zona horaria, convertir a ese offset y luego formatear
    • Un valor con nanosegundos se expresa como 2011-11-18T15:56:35.666777888Z
    • Si se especifica un offset, se expresa como 2011-11-18T18:56:35.666777888+03:00
  • time_fmt_datetime(), time_fmt_date(), time_fmt_time() devuelven respectivamente cadenas datetime, date y time
    • También pueden recibir opcionalmente un offset de zona horaria
  • time_parse() convierte una cadena con formato en un valor Time
    • Cadenas ISO 8601 con nanosegundos y zona horaria
    • Cadenas ISO 8601 con nanosegundos y Z de UTC
    • Cadenas ISO 8601 con zona horaria
    • Cadenas ISO 8601 en UTC
    • Fecha y hora UTC en formato YYYY-MM-DD HH:MM:SS
    • Fecha UTC en formato YYYY-MM-DD
    • Hora UTC en formato HH:MM:SS
  • Los layouts que admite time_parse() son un conjunto limitado

Constantes Duration

  • Se proporcionan funciones que devuelven duraciones comunes en nanosegundos
    • dur_ns() → 1
    • dur_us() → 1000
    • dur_ms() → 1000000
    • dur_s() → 1000000000
    • dur_m() → 60000000000
    • dur_h() → 3600000000000

Base de implementación e instalación

  • La extensión está implementada en C, pero su diseño e implementación se basan en gran medida en el paquete time de la biblioteca estándar de Go
    • Ese paquete usa licencia BSD 3-Clause
  • La instalación consiste en descargar el latest release y cargar la extensión en la CLI de SQLite
    • Ejemplo: .load ./time
    • Después de cargarla, pueden usarse consultas como select time_now();

Lecturas relacionadas

1 comentarios

 
GN⁺ 2024-08-16
Comentarios en Hacker News

  • Me pregunto si también maneja casos especiales como los cambios de zona horaria y las discontinuidades en la hora local, que Jon Skeet resumió de forma muy conocida
    https://stackoverflow.com/questions/6841333/why-is-subtracti...
    Computerphile también lo explica muy bien en un video de 10 minutos
    https://www.youtube.com/watch?v=-5wpm-gesOY
    Hace mucho aprendí a no crear mis propias librerías de fecha/hora ni de criptografía. Hay una cantidad interminable de casos límite que pueden salir desastrosamente mal, así que tiendo a ver con escepticismo una librería nueva como esta

    • Esta librería no maneja en absoluto el concepto de hora local. Todo está basado en UTC, y el usuario puede proporcionar un offset de zona horaria, pero la parte difícil de calcular ese offset la tiene que hacer quien la llama
      Creo que la documentación podría ser un poco más clara. El autor dice “time zones”, pero en realidad la librería solo maneja offsets de zona horaria. Una zona horaria es algo como America/New_York, y un offset de zona horaria es la diferencia con UTC. Nueva York hoy está en -14400 segundos, pero dentro de unos meses estará en -18000 segundos por el cambio de horario de verano

  • Me parecen interesantes las tres representaciones/tamaños de tiempo distintas. Por ejemplo, no sé qué caso de uso requeriría precisión de nanosegundos en un rango de miles de millones de años
    Lo más confuso es que la granularidad temporal es extremadamente fina, pero en las duraciones la precisión de nanosegundos solo cubre un rango de ±290 años

    • Una vez que decides usar precisión de nanosegundos, una representación de 64 bits solo puede abarcar 584 años, y eso no es suficiente. Harían falta al menos 2 bits más para poder representar 2024
      Pero si vas a agregar 2 bits, tampoco hay mucha razón para no agregar 16 o 32 bits. Así puedes cubrir tanto a quien calcula el tiempo que tarda la luz en recorrer 30 cm como a quien calcula la edad del universo
      Me imagino que la decisión de diseño fue por un camino más o menos así :)
      Claro, es difícil ofrecer precisión por debajo del segundo sin soporte para segundos intercalares, y tampoco está muy claro qué sentido tendría soportar segundos intercalares anteriores a la civilización humana
    • Este enfoque me ha funcionado muy bien a mí y a miles de otros desarrolladores de Go. Por eso elegí este enfoque

  • Es una tangente relacionada, pero las bases de datos deberían rastrear unidades. Si tienes una columna de tiempo, por ejemplo debería poder declararse como una duración en segundos float64
    Entonces podrías escribir algo como SELECT * FROM my_table WHERE duration_s >= 2h, y la base de datos convertiría por sí sola “2h” en 7200.0 segundos para comparar unidades equivalentes durante el escaneo de la tabla
    Hace años construí una base de datos SQL de propósito especial con este manejo nativo de unidades, pero no he vuelto a ver nada parecido antes ni después, y parece un hueco en el ecosistema de interfaces
    Tampoco tendría que limitarse al tiempo. Debería poder manejar toda la lista de unidades: masa, volumen, cantidad de información, temperatura, etc. Así la base de datos podría rechazar expresiones matemáticamente absurdas como SELECT 2h + 15kg -- type error!
    Esto ayudaría mucho a detectar errores de análisis desde temprano

  • Creo que es importante especificar si usa enteros con signo o no. Al leer la documentación, parece que sí, pero también podría no parecerlo
    Si son enteros con signo, puede haber varias cadenas de bits que representen la misma fecha y hora, y eso no es bueno

    • Definitivamente tiene signo. Dice “para restar, usa una duration negativa”
      Pero el patrón de bits es un asunto interno de la librería. Si puedes encontrar un bug en el código, desde luego vale la pena señalarlo, e idealmente también proponer una corrección
    • ¿Cómo sería posible eso de “varias cadenas de bits que representan la misma fecha y hora”?

  • Sería muy bueno que SQLite3 tuviera un sistema de tipos extensible

    • Como alguien que contribuyó un poco a PostgreSQL: no, ¡esto no se debe hacer!!!!
      Un sistema de tipos extensible es terrible para el rendimiento de los usuarios finales de una base de datos. Entonces no se puede tomar atajos en nada del análisis y la optimización de consultas. Hay que revisar el tipo de todos los operandos, encontrar la implementación correcta del operador, encontrar la familia/clase correcta de operadores de índice, y así sucesivamente, consultando constantemente el catálogo del sistema de consultas
      La entrada y salida de valores también pasa por funciones almacenadas en el catálogo del sistema. Ni siquiera select 1 se puede responder sin mirar el catálogo del sistema
      Debe haber un conjunto adecuado de tipos integrados y formas de composición como structs/JSON. La mayoría de las bases de datos, excepto PostgreSQL, funcionan así, y creo firmemente que ese es el camino correcto

  • Es una pregunta medio floja estilo Ask HN, pero por experiencia, ¿qué es más útil o valioso: una representación en nanosegundos o poder representar años fuera del rango de nanosegundos como 1678~2200?
    No hago trabajo científico serio, así que el valor de los nanosegundos me parece limitado a experimentos muy sofisticados o al seguimiento de transacciones financieras donde el rango sería más estrecho
    En cambio, la capacidad de representar fechas históricas parece algo que se necesitaría con más frecuencia. ¿Qué opinan?

    • Las fechas históricas son claramente más importantes
      Incluso si reduces la precisión a 10 nanosegundos, obtienes un rango suficientemente útil en la práctica
    • Es como preguntar qué es más útil, un martillo o un destornillador. Depende de la tarea

  • Me pregunto por qué no usar, al estilo de Go, un timestamp Unix como signed int64 en nanosegundos. No cubriría millones de años con precisión de nanosegundos, pero ¿de verdad se necesita eso?

    • Con esa precisión y tamaño, solo cubriría de 1678 a 2262, así que limita mucho la capacidad de representar fechas y horas históricas
    • Guardar un timestamp Unix en nanosegundos no es el estilo de Go, pero esta extensión sí permite hacerlo
      select time_to_nano(time_now());
      -- 1722979335431295000

  • Preferiría que expresiones como “segundos desde el epoch” se usaran solo cuando realmente significan exactamente eso
    Me pregunto qué devolvería select time_sub(time_date(2011, 11, 19), time_date(1311, 11, 18));

    • ¿Por qué lo preferirías así?
      Se me ocurren varias razones plausibles, pero lo único realmente importante es: “¿qué epoch?”. En sistemas basados en UNIX, o en sistemas que intentan imitar su comportamiento, está bien definido. Pero como no dijiste cuál es tu objeción, es difícil refutar o justificar por qué está como está ahora
      time_date(1311, 11, 18) no está definido en el epoch que usan la mayoría de los sistemas de cómputo, así que cualquier resultado es posible: MAX_INT, MIN_INT, 0, un valor plausible pero que no refleje la reforma del calendario, un valor calculado con precisión convirtiéndolo a otro epoch, etc. Incluso se podría argumentar que no hay un epoch válido antes de GMT/UTC, porque todo era hora local
      Claro, se puede argumentar en ambos sentidos si se deberían admitir valores negativos. Podrías esperar que exactamente 24 horas antes de 1970-1-1 0:00:00 UTC fuera -86400, pero “since” sugiere con bastante fuerza que sea solo positivo
      Otras personas podrían tener epochs completamente distintos por otras razones, y si todos están de acuerdo dentro del ámbito donde lo usan, eso también está bien
      ¿O tenías alguna otra objeción diferente?
    • Dice: “si el resultado excede el valor máximo que puede almacenarse en Duration, se devuelve la duration máxima”