Aritmetica De Fechas en Postgres

Tue, Jun 4, 2019

Introducción

La aritmética de fechas es una de las ramas que en general causa mas problemas a los programadores tanto de bases de datos como de aplicaciones de negocios.

Es muy importante en este tipo de aplicaciones tomar en cuenta plazos, diferentes zonas horarias, fines de semana, feriados.

Tipos de datos de fechas y horas en PostgreSQL

Lo primero que necesitamos saber son los tipos de datos disponibles para el tratamiento de las fechas y de los intervalos de tiempo. Existen 6 tipos de datos para trabajar con fechas y horas, estos valores están tomados de la documentación oficial de Postgres

Nombre	tamaño de almacenamiento	Descripción	Valor mínimo
timestamp[(p)][sin zona horaria]	8 bytes	Fecha y hora (sin zona horaria)	4713 AC
timestamp[(p)][con zona horaria]	8 bytes	Fecha y hora (con zona horaria)	4713 AC
date	4 bytes	Fecha (sin hora)	4713 AC
time [(p)] [sin zona horaria]	8 bytes	Hora (sin fecha ni zona horaria)	00:00:00
time [(p)] [con zona horaria]	12 bytes	Hora (sin fecha y con zona horaria)	00:00:00+1459
interval [campos][(p)]	16 bytes	Intervalo de tiempo	-178000000 años

La precisión p es un parámetro opcional que especifica el número de dígitos que se utilizan en el campo de los segundos. Por default no hay una cota específica para la precisión y el rango de p va de 0 a 6.

El manejo de zonas horarias es complicado, una de las razones es la adición del horario de verano. Las zonas horarias se maejan como desplazamientos de UTC, por lo que por default no se puede adaptar el horario de verano si se hace aritmética a través de zonas horarias con horario de verano.

Las zonas horarias se pueden especificar de 3 formas:

Nombre completo America/New York además del offset puede incluir reglas de horario de verano
Abreviatura como UTC este formato implica un offset de UTC y no incluye reglas de horario de verano
Estilo de reglas estillo POSIX como STDoffsetDST donde offset es un número en horas al oeste de UTC y DST es una zona de horario de verano opcional, que se asume como una hora adelante del offset dado a UTC

Como podemos ver, postgres tiene un tratamiento muy completo de las fechas y horas, permitiendo almacenar sólo fechas, sólo horas, o su combianción con diferentes niveles de precisión y tomando o no en cuenta la zona horaria. Aqui creo que es importante hacer un comentario sobre el tipo de dato interval El tipo de dato interval es parte del estándar SQL y se utiliza para almacenar precisamente intervalos de tiempo, otra de sus funciones es permitir especificar intevalos o rangos de tiempo dentro de consultas.

Para declarar un intervalo se pueden declarar intervalos como:

interval '2 months ago';
interval '3 hours 20 minutes';

El formato de los intervalos puede ser de 4 formas:

postgres verbose
ISO 8601
sql standard
postgres

En general, todos tienen una sintaxis como:

cantidad unidades [ cantidad unidades ] [ direccion ]

Podemos verlos en detalle aquí

SET intervalstyle = 'sql_standard';
 
SELECT
 INTERVAL '6 years 5 months 4 days 3 hours 2 minutes 1 second';
 
 
SET intervalstyle = 'postgres';
 
SELECT
 INTERVAL '6 years 5 months 4 days 3 hours 2 minutes 1 second';
 
 
SET intervalstyle = 'postgres_verbose';
 
SELECT
 INTERVAL '6 years 5 months 4 days 3 hours 2 minutes 1 second';
 
 
SET intervalstyle = 'iso_8601';
 
SELECT
 INTERVAL '6 years 5 months 4 days 3 hours 2 minutes 1 second';

Operaciones

Existen 4 operadores básicos para la aritmética de fechas: =, -, /, *. Estos operadores funcionan sobre los enteros, sobre los intervalos, los timestamps y las fechas.

La siguiente tabla está tomada del manual de Postgres 10.

Operator	Example	Result
+	date ‘2001-09-28’ + integer ‘7’	date ‘2001-10-05’
+	date ‘2001-09-28’ + interval ‘1 hour’	timestamp ‘2001-09-28 01:00:00’
+	date ‘2001-09-28’ + time ‘03:00’	timestamp ‘2001-09-28 03:00:00’
+	interval ‘1 day’ + interval ‘1 hour’	interval ‘1 day 01:00:00’
+	timestamp ‘2001-09-28 01:00’ + interval ‘23 hours’	timestamp ‘2001-09-29 00:00:00’
+	time ‘01:00’ + interval ‘3 hours’	time ‘04:00:00’
-	- interval ‘23 hours’	interval ‘-23:00:00’
-	date ‘2001-10-01’ - date ‘2001-09-28’	integer ‘3’ (days)
-	date ‘2001-10-01’ - integer ‘7’	date ‘2001-09-24’
-	date ‘2001-09-28’ - interval ‘1 hour’	timestamp ‘2001-09-27 23:00:00’
-	time ‘05:00’ - time ‘03:00’	interval ‘02:00:00’
-	time ‘05:00’ - interval ‘2 hours’	time ‘03:00:00’
-	timestamp ‘2001-09-28 23:00’ - interval ‘23 hours’	timestamp ‘2001-09-28 00:00:00’
-	interval ‘1 day’ - interval ‘1 hour’	interval ‘1 day -01:00:00’
-	timestamp ‘2001-09-29 03:00’ - timestamp ‘2001-09-27 12:00’	interval ‘1 day 15:00:00’
*	900 * interval ‘1 second’	interval ‘00:15:00’
*	21 * interval ‘1 day’	interval ‘21 days’
*	double precision ‘3.5’ * interval ‘1 hour’	interval ‘03:30:00’
/	interval ‘1 hour’ / double precision ‘1.5’	interval ‘00:40:00’

Además, existe una serie de funciones específicas para conversiones de fechas y manejos específicos para reglas de negocio, que se pueden ver aqui

Conclusiones

La aritmética de fechas es uno de los quebraderos de cabeza tradicionales de la programación de reglas de negocio por lo que conocer estas funciones puede llegar a salvar el día durante el desarrollo de aplicaicones y durante el diseño de bases de datos complejas.