Comment extraire le nombre total de jours, d’heures ou de minutes d’un intervalle dans PostgreSQL

Un « interval » dans PostgreSQL représente une durée. Il peut être le résultat de la soustraction de deux valeurs TIMESTAMP ou être défini explicitement avec le mot-clé INTERVAL.

Voici un exemple :

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

Résultat attendu :

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

Extraire les jours, les heures et les minutes

Extraire les jours

Pour extraire le nombre total de jours d’un intervalle :

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

Résultat attendu :

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

Extraire les heures

Pour les heures, la fonction EXTRACT ne renvoie que la composante heure de l’intervalle, et non le nombre total d’heures en incluant les jours. Par exemple :

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

Résultat attendu :

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

Pour obtenir le nombre total d’heures, jours compris, il faut faire un peu d’arithmétique :

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;

Résultat attendu :

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

Extraire les minutes

Comme pour les heures, l’extraction des minutes ne renvoie que la partie minutes de l’intervalle :

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

Résultat attendu :

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

Pour obtenir le nombre total de minutes de l’intervalle, tu peux calculer :

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;

Résultat attendu :

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

Exemples pratiques

Voyons un exemple pratique avec un tableau. Supposons qu’on ait un tableau appelé events qui stocke les timestamps de début et de fin de différents événements :

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');

On peut calculer l’intervalle de chaque événement et en extraire le nombre total de jours, d’heures et de minutes :

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;

Résultat attendu :

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

Conclusion

Extraire des jours, des heures ou des minutes d’un intervalle dans PostgreSQL est simple avec la fonction EXTRACT. Pour obtenir des totaux, tu devras combiner EXTRACT avec des opérations arithmétiques. Ces techniques sont très utiles pour analyser des données temporelles dans tes applications.

D’autres articles qui pourraient te plaire :

• Comment sélectionner uniquement les jours de semaine dans PostgreSQL
• Comment formater des dates dans PostgreSQL