Marca de tiempo automática en SQLite

SQLite puede añadir automáticamente la hora de creación mediante una columna declarada con DEFAULT CURRENT_TIMESTAMP cuando una instrucción INSERT omite esa columna. Si se proporciona explícitamente NULL, el valor predeterminado no se aplica; si la columna tiene la restricción NOT NULL, la inserción falla. SQLite no tiene una clase de almacenamiento específica para fecha y hora, así que conviene elegir una representación coherente de texto, entero o número real. En Python, sqlite3 devuelve un objeto datetime solo si se configura la conversión.

Añadir una marca de tiempo automática a una tabla de SQLite

Declara una columna de marca de tiempo con DEFAULT CURRENT_TIMESTAMP; por ejemplo: CREATE TABLE events (id INTEGER PRIMARY KEY, name TEXT NOT NULL, created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP);. Una instrucción como INSERT INTO events (name) VALUES ('launch'); omite created_at, por lo que SQLite utiliza el valor predeterminado. Si una inserción incluye explícitamente created_at y proporciona NULL, no se aplica el valor predeterminado y la restricción NOT NULL rechaza la inserción. Mantén los mismos nombres de tabla y columna en las consultas posteriores, como SELECT id, name, created_at FROM events;.

CURRENT_TIMESTAMP es una palabra clave de SQLite que proporciona la fecha y hora UTC actuales como texto con el formato YYYY-MM-DD HH:MM:SS. El valor predeterminado se aplica a las filas insertadas; no cambia automáticamente cuando se actualiza otra columna. Si necesitas registrar la hora de actualización, establécela en la instrucción de actualización o utiliza un disparador diseñado para ese comportamiento.

Elegir una representación de fecha y hora en SQLite

Los valores de SQLite utilizan las clases de almacenamiento TEXT, INTEGER y REAL (además de NULL y BLOB). Declarar un nombre como DATETIME o TIMESTAMP no crea un tipo de almacenamiento nativo para fecha y hora. Las reglas de afinidad de tipos de SQLite siguen determinando cómo se almacenan los valores, así que elige una representación y úsala de forma coherente.

El texto es legible y funciona bien para fechas cuyos componentes se ordenan de mayor a menor importancia, como YYYY-MM-DD HH:MM:SS.SSS. Los enteros pueden almacenar la hora Unix en segundos, mientras que los números reales pueden almacenar valores de día juliano. Las funciones de fecha y hora de SQLite pueden interpretar estas representaciones. DATE_ADD y UNIX_TIMESTAMP no son funciones de SQLite; para las conversiones compatibles, utiliza las funciones de SQLite date(), time() y datetime().

Recuperar marcas de tiempo de SQLite como objetos datetime de Python

El módulo sqlite3 devuelve los valores ordinarios de SQLite como valores de Python según su clase de almacenamiento; una cadena con aspecto de marca de tiempo normalmente se devuelve como str. Para obtener un datetime de Python, registra un convertidor y activa el análisis del tipo declarado. Este ejemplo independiente crea una base de datos en memoria, inserta una marca de tiempo fija e imprime el valor convertido y su tipo.

Este ejemplo autónomo configura la conversión y muestra cómo la consulta devuelve la marca de tiempo como un objeto datetime de Python:

import datetime
import sqlite3


def convert_timestamp(raw_value):
    return datetime.datetime.fromisoformat(raw_value.decode("utf-8"))


sqlite3.register_converter("timestamp", convert_timestamp)
connection = sqlite3.connect(":memory:", detect_types=sqlite3.PARSE_DECLTYPES)
connection.execute("CREATE TABLE events (created_at TIMESTAMP NOT NULL)")
connection.execute("INSERT INTO events VALUES (?)", ("2024-02-03 04:05:06",))
created_at = connection.execute("SELECT created_at FROM events").fetchone()[0]
print(created_at.isoformat(sep=" "))
print(type(created_at).__name__)
connection.close()

2024-02-03 04:05:06
datetime

La conversión se configura en Python para la conexión; SQLite sigue almacenando el valor según sus propias reglas. Si no se registra un convertidor o no se activa el análisis, selecciona el valor como texto y procésalo en el código de la aplicación cuando sea necesario.

Convertir marcas de tiempo Unix a fechas y horas en SQLite

Pasa una marca de tiempo Unix entera y el modificador unixepoch a las funciones de fecha y hora de SQLite. Para un valor fijo, datetime(1793956207, 'unixepoch') devuelve una fecha y una hora, date(1793956207, 'unixepoch') devuelve la fecha y time(1793956207, 'unixepoch') devuelve la hora del día. Estas expresiones utilizan el mismo instante y solo difieren en la parte que devuelven. Una expresión que utilice la hora actual variará según el momento en que se evalúe, así que usa un valor fijo cuando necesites un ejemplo reproducible.

Almacenar fechas y horas como texto

Una representación de texto como 2019-02-02 11:21:08.321 sigue el patrón YYYY-MM-DD HH:MM:SS.SSS. Es una convención de formato almacenada con la clase de almacenamiento TEXT de SQLite, no un tipo especial para fecha y hora. Mantén coherentes el formato, la precisión y la convención de zona horaria en todas las filas. Si los valores usan un formato coherente que coloca primero los componentes de fecha más significativos, se pueden comparar lexicográficamente en orden cronológico; mezclar formatos o convenciones de zona horaria puede dar lugar a comparaciones engañosas.