Cómo extraer el total de días, horas o minutos de un intervalo en PostgreSQL

Un ‘interval’ en PostgreSQL representa una duración de tiempo. Puede ser el resultado de restar dos valores TIMESTAMP o definirse de forma explícita con la palabra clave INTERVAL.

Aquí tienes un ejemplo:

SELECT INTERVAL '2 days 3 hours 40 minutes' AS example_interval;

Resultado esperado:

example_interval
-------------------
2 days 03:40:00

Cómo extraer días, horas y minutos

Extraer días

Para extraer el número total de días de un intervalo:

SELECT EXTRACT(DAY FROM INTERVAL '5 days 4 hours 30 minutes') AS total_days;

Resultado esperado:

total_days
------------
5

Extraer horas

Al extraer horas, la función EXTRACT solo devuelve el componente de horas del intervalo, no el total de horas incluyendo los días. Por ejemplo:

SELECT EXTRACT(HOUR FROM INTERVAL '2 days 4 hours 30 minutes') AS hours_component;

Resultado esperado:

hours_component
-----------------
4

Para obtener el total de horas, incluyendo los días, necesitas hacer un poco de aritmética:

SELECT EXTRACT(DAY FROM INTERVAL '2 days 4 hours 30 minutes') * 24 +
       EXTRACT(HOUR FROM INTERVAL '2 days 4 hours 30 minutes') AS total_hours;

Resultado esperado:

total_hours
------------
52

Extraer minutos

Igual que con las horas, extraer minutos solo devuelve la parte de minutos del intervalo:

SELECT EXTRACT(MINUTE FROM INTERVAL '2 days 4 hours 30 minutes') AS minutes_component;

Resultado esperado:

minutes_component
-------------------
30

Para obtener el total de minutos del intervalo, puedes calcular:

SELECT (EXTRACT(DAY FROM INTERVAL '2 days 4 hours 30 minutes') * 24 * 60) +
       (EXTRACT(HOUR FROM INTERVAL '2 days 4 hours 30 minutes') * 60) +
       EXTRACT(MINUTE FROM INTERVAL '2 days 4 hours 30 minutes') AS total_minutes;

Resultado esperado:

total_minutes
---------------
3150

Ejemplos prácticos

Veamos un ejemplo práctico con una tabla. Supongamos que tenemos una tabla llamada events que guarda los timestamps de inicio y fin de varios eventos:

CREATE TABLE events (
    event_id SERIAL PRIMARY KEY,
    start_time TIMESTAMP,
    end_time TIMESTAMP
);

INSERT INTO events (start_time, end_time)
VALUES 
    ('2024-08-01 08:00:00', '2024-08-03 12:30:00'),
    ('2024-08-05 09:15:00', '2024-08-05 18:45:00');

Podemos calcular el intervalo de cada evento y extraer el total de días, horas y minutos:

SELECT 
    event_id,
    end_time - start_time AS duration,
    EXTRACT(DAY FROM end_time - start_time) AS total_days,
    (EXTRACT(DAY FROM end_time - start_time) * 24 +
     EXTRACT(HOUR FROM end_time - start_time)) AS total_hours,
    (EXTRACT(DAY FROM end_time - start_time) * 24 * 60 +
     EXTRACT(HOUR FROM end_time - start_time) * 60 +
     EXTRACT(MINUTE FROM end_time - start_time)) AS total_minutes
FROM events;

Resultado esperado:

event_id |   duration    | total_days | total_hours | total_minutes
---------+---------------+------------+-------------+--------------
       1 | 2 days 04:30  |          2 |          52 |          3150
       2 | 09:30:00      |          0 |           9 |           570

Conclusión

Extraer días, horas o minutos de un intervalo en PostgreSQL es sencillo con la función EXTRACT. Para obtener totales, tendrás que combinar EXTRACT con operaciones aritméticas. Estas técnicas son muy útiles para analizar datos basados en tiempo en tus aplicaciones.

Otros artículos que te pueden gustar:

• Cómo seleccionar solo días hábiles en PostgreSQL
• Cómo dar formato a fechas en PostgreSQL