HN destacado: funciones de fecha/hora de alta precisión en SQLite

 (antonz.org)
2 puntos por GN⁺ 2024-08-16 | 1 comentarios | Compartir por WhatsApp

Extensión de fecha/hora de alta precisión

SQLite ofrece funciones básicas de fecha, pero como se necesitaban más capacidades, se creó una extensión de fecha/hora de alta precisión llamada sqlean-time. Esta extensión ofrece una API estructurada y varias funciones.

Nota. Agregar extensiones a SQLite es muy sencillo. Basta con descargar el archivo y ejecutar un solo comando de base de datos.

Conceptos

Esta extensión usa dos tipos de valores: tiempo (Time) y duración (Duration).

  • Tiempo (Time): un par compuesto por segundos y nanosegundos, que representa los segundos transcurridos desde la hora cero (0001-01-01 00:00:00 UTC) y los nanosegundos dentro del segundo actual.

    • Se puede almacenar el tiempo en su representación interna, lo que permite representar fechas miles de millones de años hacia atrás o hacia adelante con precisión de nanosegundos.
    • También se puede almacenar el tiempo como segundos desde la época Unix (1970-01-01 00:00:00 UTC), incluyendo milisegundos, microsegundos y nanosegundos.
    • El tiempo siempre se almacena y opera en UTC, pero puede convertirse a un desplazamiento de zona horaria específico.

  • Duración (Duration): un número de 64 bits en nanosegundos, que puede representar valores de hasta aproximadamente 290 años.

Crear valores de tiempo

  • Hora actual:

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

  • Fecha/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

Extraer campos de tiempo

Hay funciones para extraer varios campos de fecha/hora:

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

Tiempo Unix

Funciones para crear valores de tiempo a partir del tiempo Unix (tiempo desde 1970-01-01 UTC):

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

Funciones para convertir valores de tiempo a tiempo Unix:

select time_to_unix(time_now());  -- 1722979335

Comparación de tiempo

Funciones para comparar valores de tiempo:

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

Operaciones de tiempo

Funciones para sumar duraciones a valores de tiempo:

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

Constantes de duración:

  • dur_us() - 1 microsegundo
  • dur_ms() - 1 milisegundo
  • dur_s() - 1 segundo
  • dur_m() - 1 minuto
  • dur_h() - 1 hora

Redondeo

Funciones para redondear valores de tiempo a la precisión del 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;

Formateo

Funciones que devuelven cadenas de tiempo 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 duración

Funciones que devuelven duraciones comunes en nanosegundos:

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

Agradecimientos

Esta extensión fue implementada en C, y su diseño e implementación se basan en el paquete de tiempo de la biblioteca estándar de Go (licencia BSD 3-Clause).

Instalación y uso

  1. Descargar la última versión
  2. Usar en la interfaz de línea de comandos de SQLite: 
    sqlite> .load ./time
sqlite> select time_now();

Resumen de GN⁺

  • La extensión sqlean-time agrega funciones de fecha/hora de alta precisión a SQLite, haciendo posibles diversas operaciones temporales.
  • Puede manejar tiempo y duración en nanosegundos, lo que permite cálculos temporales muy precisos.
  • Ofrece varias funciones de formateo y comparación de tiempo para que los desarrolladores las usen fácilmente.
  • Proporciona muchas más funciones que las funciones básicas de fecha de SQLite, por lo que es útil para proyectos que requieren operaciones temporales complejas.

Lecturas relacionadas

1 comentarios

 
GN⁺ 2024-08-16
Comentarios de Hacker News

  • Pregunta sobre si maneja casos especiales de cambios de zona horaria y discontinuidades del tiempo local documentados por Jon Skeet

    • Enlace relacionado: Stack Overflow
    • Enlace a la explicación en video de 10 minutos de Computerphile: YouTube

  • Es mejor no construir por cuenta propia librerías de fecha/hora ni de criptografía

    • Los casos límite interminables pueden causar problemas
    • Razón para ser escéptico al encontrarse con una librería nueva

  • Resultan interesantes las tres representaciones/tamaños distintos del tiempo

    • Se pregunta qué casos de uso requieren precisión de nanosegundos durante escalas de miles de millones de años
    • Confunde que solo se ofrezca un rango de ±290 años con precisión de nanosegundos

  • Es importante dejar claro si se usan enteros con signo

    • Al leer la documentación, podría interpretarse que son enteros con signo o que no lo son
    • Si fueran enteros con signo, podría haber múltiples cadenas de bits que representen la misma fecha y hora

  • Ojalá SQLite3 tuviera un sistema de tipos extensible

  • Se considera muy genial, aunque se mencionan funciones importantes que le faltan a SQLite

  • Se argumenta que la base de datos debería rastrear unidades

    • Por ejemplo, debería poder especificarse que una columna de tiempo representa segundos en float64
    • La base de datos debería poder convertir "2h" en 7200.0 segundos y compararlo durante un escaneo de tabla
    • En el pasado escribió una base de datos SQL de propósito especial que manejaba este tipo de unidades, pero no ha vuelto a ver algo así desde entonces
    • Debería poder manejar no solo tiempo, sino todas las unidades como masa, volumen, información, temperatura, etc.
    • Se le podría enseñar a la base de datos a rechazar operaciones matemáticamente absurdas para detectar errores matemáticos desde temprano

  • Pregunta sobre qué es más útil: la representación en nanosegundos o los años fuera del rango nano

    • Como no hace ciencia "exacta", el valor de los nanosegundos es limitado
    • Parece más probable necesitar con mayor frecuencia la capacidad de representar fechas históricas

  • Se propone usar marcas de tiempo Unix al estilo de golang en nanosegundos, como int64 con signo

    • Puede que no cubra millones de años con precisión de nanosegundos, pero se duda que eso realmente sea necesario

  • Se argumenta que no debería usarse la expresión "segundos desde la época" a menos que signifique exactamente eso

    • Consulta de ejemplo: select time_sub(time_date(2011, 11, 19), time_date(1311, 11, 18));