Come estrarre il totale di giorni, ore o minuti da un intervallo in PostgreSQL

Un ‘interval’ in PostgreSQL rappresenta una durata di tempo. Può essere il risultato della sottrazione tra due valori TIMESTAMP oppure essere definito esplicitamente con la parola chiave INTERVAL.

Ecco un esempio:

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

Output atteso:

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

Estrarre giorni, ore e minuti

Estrarre i giorni

Per estrarre il numero totale di giorni da un intervallo:

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

Output atteso:

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

Estrarre le ore

Quando estrai le ore, la funzione EXTRACT restituisce solo la componente delle ore dell'intervallo, non il totale delle ore compresi i giorni. Per esempio:

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

Output atteso:

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

Per ottenere il totale delle ore, compresi i giorni, devi fare un po' di aritmetica:

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;

Output atteso:

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

Estrarre i minuti

Come per le ore, estrarre i minuti restituisce solo la parte dei minuti dell'intervallo:

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

Output atteso:

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

Per ottenere il totale dei minuti dell'intervallo, puoi calcolare:

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;

Output atteso:

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

Esempi pratici

Vediamo un esempio pratico con una tabella. Supponiamo di avere una tabella chiamata events che memorizza i timestamp di inizio e fine di vari eventi:

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

Possiamo calcolare l'intervallo per ogni evento ed estrarre il totale di giorni, ore e minuti:

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;

Output atteso:

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

Conclusione

Estrarre giorni, ore o minuti da un intervallo in PostgreSQL è semplice con la funzione EXTRACT. Per i totali, dovrai combinare EXTRACT con operazioni aritmetiche. Queste tecniche sono molto utili per analizzare dati temporali nelle tue applicazioni.

Altri articoli che potrebbero piacerti:

• Come selezionare solo i giorni feriali in PostgreSQL
• Come formattare le date in PostgreSQL