Filas temporales y versionadas
Consultas de tiempo válido y de estado en una fecha determinada.
Filas temporales y versionadas es una lección gratuita de SQL Academy en CoddyKit. Esta es la lección 3 de 4. Puedes leer la lección completa abajo gratuitamente — luego la practicas en el navegador con un editor de código integrado y un tutor de IA 24/7. Forma parte de la ruta de aprendizaje de SQL Academy, y tu progreso se sincroniza en la web y la app de CoddyKit. El curso de SQL Academy incluye 4 lecciones en total.
¿Qué son las tablas temporales?
Las tablas temporales permiten realizar un seguimiento de cómo cambian los datos a lo largo del tiempo. En lugar de sobrescribir una fila cuando algo cambia, una tabla temporal conserva cada versión de esa fila, marcada con el periodo durante el que fue válida.
Hay dos conceptos clave: el tiempo de validez (cuándo era cierto el hecho en el mundo real) y el tiempo de transacción (cuándo registró el hecho la base de datos). La combinación de ambos da lugar a una tabla completamente bitemporal.
Tiempo de validez frente a tiempo de transacción
El tiempo de validez representa cuándo es cierto un hecho en el mundo real; por ejemplo, el salario de un empleado del 2020-01-01 al 2022-06-30. El tiempo de transacción indica cuándo se insertó o expiró la fila en la base de datos. Juntos responden a dos preguntas: ¿Qué era cierto? y ¿Cuándo lo supimos?
La mayoría de los casos prácticos comienzan con el seguimiento del tiempo de validez, que puede implementarse manualmente mediante las columnas valid_from y valid_to.
Creación de una tabla de tiempo de validez
La forma más sencilla de almacenar filas versionadas consiste en añadir columnas de marca de tiempo valid_from y valid_to. Un valor NULL en valid_to (o un valor centinela muy lejano en el futuro, como 9999-12-31) indica que la fila está activa actualmente.
CREATE TABLE employee_salary (
id SERIAL PRIMARY KEY,
employee_id INT NOT NULL,
salary NUMERIC(12, 2) NOT NULL,
valid_from DATE NOT NULL,
valid_to DATE
);
INSERT INTO employee_salary (employee_id, salary, valid_from, valid_to)
VALUES
(1, 50000, '2020-01-01', '2022-06-30'),
(1, 60000, '2022-07-01', NULL);Consulta de la versión actual
Para encontrar la fila actualmente activa de cada empleado, filtre las filas cuyo valid_to IS NULL (sin límite superior) o aquellas en las que la fecha actual se encuentre dentro del intervalo de validez. El uso de un valor centinela como '9999-12-31' simplifica las comparaciones de rangos.
SELECT employee_id, salary
FROM employee_salary
WHERE valid_to IS NULL
ORDER BY employee_id;Consultas as-of
Una consulta as-of pregunta: ¿Cuáles eran los datos en un momento específico? Se filtran las filas en las que la marca de tiempo indicada se encuentra dentro del periodo de validez. Esta es una de las funciones más potentes de las tablas temporales.
-- What was employee 1's salary on 2021-03-15?
SELECT employee_id, salary, valid_from, valid_to
FROM employee_salary
WHERE employee_id = 1
AND valid_from <= '2021-03-15'
AND (valid_to IS NULL OR valid_to > '2021-03-15');Actualización de una fila versionada
Cuando cambia un hecho, no se ejecuta un UPDATE sobre la fila existente. En su lugar, se cierra la fila actual estableciendo su valid_to y se INSERTA una fila nueva con el valor actualizado. Así se conserva el historial completo.
-- Employee 1 gets a raise effective 2023-01-01
BEGIN;
-- Close the current open row
UPDATE employee_salary
SET valid_to = '2022-12-31'
WHERE employee_id = 1
AND valid_to IS NULL;
-- Insert the new version
INSERT INTO employee_salary (employee_id, salary, valid_from, valid_to)
VALUES (1, 72000, '2023-01-01', NULL);
COMMIT;Uso de daterange para los periodos de validez
El tipo daterange de PostgreSQL modela elegantemente un periodo de validez como una sola columna. Puede utilizar el operador @> (contiene) para comprobar si una fecha pertenece al rango y añadir una restricción de exclusión para evitar periodos superpuestos de una misma entidad.
CREATE TABLE employee_salary_v2 (
id SERIAL PRIMARY KEY,
employee_id INT NOT NULL,
salary NUMERIC(12, 2) NOT NULL,
valid_period DATERANGE NOT NULL,
EXCLUDE USING GIST (employee_id WITH =, valid_period WITH &&)
);
INSERT INTO employee_salary_v2 (employee_id, salary, valid_period)
VALUES
(1, 50000, '[2020-01-01, 2022-07-01)'),
(1, 60000, '[2022-07-01, infinity)');Consulta as-of con daterange
Con el enfoque de daterange, la consulta as-of resulta muy legible. El operador @> comprueba que la fecha indicada esté contenida en el rango y gestiona automáticamente los límites inferior y superior.
-- What was employee 1's salary on 2021-03-15?
SELECT employee_id, salary, valid_period
FROM employee_salary_v2
WHERE employee_id = 1
AND valid_period @> '2021-03-15'::date;Tablas con versionado del sistema (estándar SQL)
El estándar SQL:2011 introdujo las tablas temporales con versionado del sistema. La base de datos gestiona automáticamente las columnas de tiempo de transacción row_start y row_end. En PostgreSQL esto se simula; en SQL Server y MariaDB está integrado mediante SYSTEM VERSIONING.
El ejemplo siguiente muestra la sintaxis de SQL Server y MariaDB como referencia para este concepto.
-- SQL Server / MariaDB syntax (reference)
CREATE TABLE dbo.Product (
ProductID INT PRIMARY KEY,
Name VARCHAR(100),
Price DECIMAL(10,2),
SysStart DATETIME2 GENERATED ALWAYS AS ROW START,
SysEnd DATETIME2 GENERATED ALWAYS AS ROW END,
PERIOD FOR SYSTEM_TIME (SysStart, SysEnd)
)
WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.Product_History));Uniones temporales: alineación de dos tablas en el tiempo
Un desafío habitual consiste en unir dos tablas temporales cuyos periodos de tiempo coincidan. Por ejemplo, unir los salarios de los empleados con sus asignaciones departamentales cuando ambas tienen periodos de tiempo de validez. La unión se realiza por la clave de entidad AND la condición de solapamiento, utilizando && en los rangos o comparaciones explícitas de fechas.
CREATE TABLE dept_assignment (
employee_id INT,
department VARCHAR(50),
valid_period DATERANGE
);
INSERT INTO dept_assignment VALUES
(1, 'Engineering', '[2020-01-01, infinity)'),
(1, 'Marketing', '[2019-01-01, 2020-01-01)');
-- Periods where employee 1 was in Engineering AND had salary > 55000
SELECT s.salary, d.department,
s.valid_period * d.valid_period AS overlap_period
FROM employee_salary_v2 s
JOIN dept_assignment d
ON s.employee_id = d.employee_id
AND s.valid_period && d.valid_period
WHERE s.employee_id = 1
AND s.salary > 55000;Prevención de lagunas y solapamientos
Dos problemas habituales de calidad de datos en las tablas temporales son las lagunas (periodos sin ningún registro) y los solapamientos (dos filas válidas simultáneamente). La restricción de exclusión con && evita los solapamientos en el nivel de la base de datos. Para detectar lagunas es necesario comprobar mediante una consulta si falta cobertura.
-- Find gaps in salary history for employee 1
-- (periods where upper(prev) < lower(next))
SELECT
upper(a.valid_period) AS gap_start,
lower(b.valid_period) AS gap_end
FROM employee_salary_v2 a
JOIN employee_salary_v2 b
ON a.employee_id = b.employee_id
AND upper(a.valid_period) < lower(b.valid_period)
WHERE a.employee_id = 1
AND NOT EXISTS (
SELECT 1 FROM employee_salary_v2 c
WHERE c.employee_id = 1
AND lower(c.valid_period) > upper(a.valid_period)
AND lower(c.valid_period) < lower(b.valid_period)
)
ORDER BY gap_start;Comprobación de conocimientos
Compruebe sus conocimientos sobre tablas temporales y consultas as-of.
Repaso: filas temporales y versionadas
En esta lección ha aprendido a modelar datos que varían con el tiempo mediante columnas de tiempo de validez y el tipo daterange de PostgreSQL. Ideas clave:
- Nunca sobrescriba las filas históricas: cierre la anterior e inserte una versión nueva.
- Utilice consultas as-of (
valid_from <= target AND valid_to > target) para recuperar los datos de cualquier momento pasado. - El tipo
daterange, junto con el operador@>, hace que las consultas temporales sean concisas y fáciles de leer. - Las restricciones de exclusión sobre
&&(solapamiento de rangos) garantizan la integridad de los datos en el nivel de la base de datos. - Las uniones temporales alinean dos historiales intersectando sus periodos de validez.
Estos patrones constituyen la base de event sourcing, el registro de auditoría y cualquier sistema en el que la precisión histórica sea importante.
Preguntas frecuentes
¿La lección «Filas temporales y versionadas» es gratis?
Sí — el texto completo de «Filas temporales y versionadas» es gratis para leer aquí en la web. Para practicarla de forma interactiva (editor de código integrado y tutor de IA 24/7) y desbloquear el resto del curso de SQL Academy, actualiza a CoddyKit PRO. El curso de SQL Academy incluye 4 lecciones en total.
¿Qué aprenderé en «Filas temporales y versionadas»?
Consultas de tiempo válido y de estado en una fecha determinada. Practicas SQL Academy con código real que ejecutas directamente en el navegador, y un tutor de IA 24/7 responde tus preguntas mientras trabajas en la lección.
¿Necesito experiencia previa para empezar SQL Academy?
No se requiere experiencia previa. SQL Academy en CoddyKit está estructurado para principiantes hasta estudiantes avanzados, así que puedes empezar aquí o desde el inicio y avanzar a tu ritmo. Esta es la lección 3 de 4.
¿Cuánto tiempo toma la lección «Filas temporales y versionadas»?
La mayoría de las lecciones de CoddyKit toman alrededor de 5–10 minutos. Cada una es compacta e interactiva, así que avanzas constantemente y retomas exactamente por donde dejaste en la web y la app.
¿Puedo escribir y ejecutar código en esta lección de SQL Academy?
Sí. Cada lección de SQL Academy incluye un editor de código integrado, así que escribes y ejecutas código real directamente en tu navegador y obtienes retroalimentación instantánea de IA — sin configuración local necesaria.
Todas las lecciones de este curso
- Por qué conservar el historial
- Tablas de eventos de solo anexado
- Filas temporales y versionadas
- Reconstrucción del estado a partir de eventos