Come lavorare con date e orari in SQL

Introduzione

Quando si lavora con database relazionali e Linguaggio di Query Strutturato (SQL), ci possono essere momenti in cui è necessario lavorare con valori che rappresentano date o orari specifici. Ad esempio, potresti aver bisogno di calcolare le ore totali trascorse su una determinata attività, o forse è necessario manipolare valori di data o ora utilizzando operatori matematici e funzioni aggregate per calcolarne la somma o la media.

In questo tutorial, imparerai come utilizzare date e orari in SQL. Inizierai eseguendo operazioni aritmetiche e utilizzando varie funzioni con date e orari utilizzando solo l’istruzione SELECT. Poi pratica eseguendo query su dati di esempio, e imparerai come implementare la funzione CAST per rendere l’output più digeribile da leggere.

Prerequisiti

Per completare questo tutorial, avrai bisogno di:

• A server running Ubuntu 20.04, with a non-root user with sudo administrative privileges and firewall enabled. Follow our Initial Server Setup with Ubuntu 20.04 to get started.
• MySQL installato e sicuro sul server. Segui la nostra guida Come Installare MySQL su Ubuntu 20.04 per configurarlo. Questa guida presuppone che tu abbia anche configurato un utente MySQL non-root, come indicato nel Passaggio 3 di questa guida.

Per praticare l’utilizzo della data e dell’ora in questo tutorial, avrai bisogno di un database e di una tabella caricati con dati di esempio. Se non ne hai uno pronto per l’inserimento, puoi leggere la seguente sezione Connessione a MySQL e Configurazione di un Database di Esempio per imparare come creare un database e una tabella. Questo tutorial farà riferimento a questo database di esempio e alla tabella in tutto.

Connessione a MySQL e Configurazione di un Database di Esempio

Se il tuo database SQL è in esecuzione su un server remoto, accedi via SSH al tuo server dalla tua macchina locale:

ssh sammy@your_server_ip

Successivamente, apri il prompt MySQL, sostituendo sammy con le informazioni del tuo account utente MySQL:

mysql -u sammy -p

Crea un database chiamato datetimeDB:

CREATE DATABASE datetimeDB;

Se il database è stato creato con successo, riceverai il seguente output:

Output
Query OK, 1 row affected (0.01 sec)

Per selezionare il database datetimeDB, esegui la seguente istruzione USE:

USE datetimeDB;

Output
Database changed

Dopo aver selezionato il database, crea una tabella al suo interno. Per l’esempio di questo tutorial, creeremo una tabella che contiene i risultati di due corridori per varie gare disputate nell’arco di un anno. Questa tabella conterrà le seguenti sette colonne:

• race_id: visualizza valori del tipo di dati int e funge da chiave primaria della tabella, il che significa che ogni valore in questa colonna funzionerà come un identificatore unico per la relativa riga.
• runner_name: utilizza il tipo di dati varchar con un massimo di 30 caratteri per i nomi dei due corridori, Bolt e Felix.
• race_name: contiene i tipi di gare con il tipo di dati varchar con un massimo di 20 caratteri.
• start_day: utilizza il tipo di dati DATE per tenere traccia della data di una specifica gara per anno, mese e giorno. Questo tipo di dati segue i seguenti parametri: quattro cifre per l’anno e un massimo di due cifre per il mese e il giorno (YYYY-MM-DD).
• start_time: rappresenta l’ora di inizio della gara con il tipo di dati TIME per ore, minuti e secondi (HH:MM:SS). Questo tipo di dati segue un formato orario a 24 ore, come 15:00 per l’equivalente delle 15:00.
• total_miles: mostra il chilometraggio totale per ogni gara utilizzando il tipo di dati decimal poiché molti dei chilometri totali per gara non sono numeri interi. In questo caso, decimal specifica una precisione di tre con una scala di uno, il che significa che i valori in questa colonna possono avere tre cifre, con una di quelle cifre che si trova alla destra del punto decimale.
• end_time: utilizza il tipo di dati TIMESTAMP per tracciare i tempi dei corridori alla fine della gara. Questo tipo di dati combina sia la data che l’ora in una stringa, e il suo formato è una combinazione di quelli di DATE e TIME: (YYYY-MM-DD HH:MM:SS).

Crea la tabella eseguendo il comando CREATE TABLE:

CREATE TABLE race_results (
race_id int, 
runner_name varchar(30),
race_name varchar(20), 
start_day DATE,
start_time TIME, 
total_miles decimal(3, 1),
end_time TIMESTAMP,
PRIMARY KEY (race_id)
); 

Successivamente inserisci alcuni dati di esempio nella tabella vuota:

INSERT INTO race_results
(race_id, runner_name, race_name, start_day, start_time, total_miles, end_time)
VALUES
(1, 'bolt', '1600_meters', '2022-09-18', '7:00:00', 1.0, '2022-09-18 7:06:30'),
(2, 'bolt', '5K', '2022-10-19', '11:00:00', 3.1, '2022-10-19 11:22:31'),
(3, 'bolt', '10K', '2022-11-20', '10:00:00', 6.2, '2022-11-20 10:38:05'),
(4, 'bolt', 'half_marathon', '2022-12-21', '6:00:00', 13.1, '2022-12-21 07:39:04'),
(5, 'bolt', 'full_marathon', '2023-01-22', '8:00:00', 26.2, '2023-01-22 11:23:10'),
(6, 'felix', '1600_meters', '2022-09-18', '7:00:00', 1.0, '2022-09-18 7:07:15'),
(7, 'felix', '5K', '2022-10-19', '11:00:00', 3.1, '2022-10-19 11:30:50'),
(8, 'felix', '10K', '2022-11-20', '10:00:00', 6.2, '2022-11-20 11:10:17'),
(9, 'felix', 'half_marathon', '2022-12-21', '6:00:00', 13.1, '2022-12-21 08:11:57'),
(10, 'felix', 'full_marathon', '2023-01-22', '8:00:00', 26.2, '2023-01-22 12:02:10');

Output
Query OK, 10 rows affected (0.00 sec)
Records: 10  Duplicates: 0  Warnings: 0

Una volta inseriti i dati, sei pronto per iniziare a praticare alcune operazioni aritmetiche e funzioni con date e ore in SQL.

Utilizzo dell’aritmetica con date e orari

In SQL, puoi manipolare i valori di data e ora utilizzando espressioni matematiche. Tutto ciò che serve è l’operatore matematico e i valori che desideri calcolare.

Come esempio, diciamo che vuoi trovare una data che sia un certo numero di giorni dopo un’altra. La query seguente prende un valore di data (2022-10-05) e aggiunge 17 ad esso per restituire il valore per la data diciassette giorni dopo quella specificata nella query. Nota che questo esempio specifica 2022-10-05 come un valore DATE per assicurarsi che il DBMS non lo interpreti come una stringa o qualche altro tipo di dato:

SELECT DATE '2022-10-05' + 17 AS new_date;

Output
+----------+
| new_date |
+----------+
| 20221022 |
+----------+
1 row in set (0.01 sec)

Come indica questo output, 17 giorni dopo 2022-10-05 è 2022-10-22, o 22 ottobre 2022.

Come altro esempio, diciamo che vuoi calcolare il totale delle ore tra due orari diversi. Puoi farlo sottraendo i due orari l’uno dall’altro. Per la seguente query, 11:00 è il primo valore di ora e 3:00 è il secondo valore di ora. Qui è necessario specificare che entrambi sono valori TIME per restituire la differenza in ore:

SELECT TIME '11:00' - TIME '3:00' AS time_diff;

Output
+-----------+
| time_diff |
+-----------+
|     80000 |
+-----------+
1 row in set (0.00 sec)

Questo output ti dice che la differenza tra le 11:00 e le 3:00 è 80000, o 8 ore.

Ora pratica usando l’aritmetica sulle informazioni di data e ora dai dati di esempio. Per la prima query, calcola il tempo totale impiegato dai corridori per terminare ogni gara sottraendo end_time da start_time:

SELECT runner_name, race_name, end_time - start_time 
AS total_time 
FROM race_results;

Output
+-------------+---------------+----------------+
| runner_name | race_name     | total_time     |
+-------------+---------------+----------------+
| bolt        | 1600_meters   | 20220918000630 |
| bolt        | 5K            | 20221019002231 |
| bolt        | 10K           | 20221120003805 |
| bolt        | half_marathon | 20221221013904 |
| bolt        | full_marathon | 20230122032310 |
| felix       | 1600_meters   | 20220918000715 |
| felix       | 5K            | 20221019003050 |
| felix       | 10K           | 20221120011017 |
| felix       | half_marathon | 20221221021157 |
| felix       | full_marathon | 20230122040210 |
+-------------+---------------+----------------+
10 rows in set (0.00 sec)

Noterai che questo output nella colonna total_time è piuttosto lungo e difficile da leggere. Più avanti, dimostreremo come utilizzare la funzione CAST per convertire questi valori dati in modo che siano più chiari da leggere.

Ora, se sei interessato solo alle prestazioni di ciascun corridore per gare più lunghe, come mezze maratone e maratone complete, puoi interrogare i tuoi dati per recuperare tali informazioni. Per questa query, sottrai end_time da start_time, e restringi i risultati utilizzando la clausola WHERE per recuperare dati in cui total_miles era maggiore di 12:

SELECT runner_name, race_name, end_time - start_time AS half_full_results
FROM race_results 
WHERE total_miles > 12;

Output
+-------------+---------------+-------------------+
| runner_name | race_name     | half_full_results |
+-------------+---------------+-------------------+
| bolt        | half_marathon |    20221221013904 |
| bolt        | full_marathon |    20230122032310 |
| felix       | half_marathon |    20221221021157 |
| felix       | full_marathon |    20230122040210 |
+-------------+---------------+-------------------+
4 rows in set (0.00 sec)

In questa sezione, hai eseguito alcune operazioni aritmetiche su date e orari con l’istruzione SELECT e per scopi pratici sui dati di esempio. Successivamente, praticherai query utilizzando varie funzioni di data e ora.

Utilizzo di Funzioni di Data e Ora ed Espressioni di Intervallo

Esistono diverse funzioni che possono essere utilizzate per trovare e manipolare valori di data e ora in SQL. Le funzioni SQL sono tipicamente utilizzate per elaborare o manipolare dati, e le funzioni disponibili dipendono dall’implementazione SQL. La maggior parte delle implementazioni SQL, comunque, ti permette di trovare la data e l’ora correnti interrogando i valori di current_date e current_time.

Per trovare la data odierna, ad esempio, la sintassi è breve e composta solo dall’istruzione SELECT e dalla funzione current_date come nel seguente esempio:

SELECT current_date;

Output
+--------------+
| current_date |
+--------------+
| 2022-02-15   |
+--------------+
1 row in set (0.00 sec)

Utilizzando la stessa sintassi, puoi trovare l’ora corrente con la funzione current_time:

SELECT current_time;

Output
+--------------+
| current_time |
+--------------+
| 17:10:20     |
+--------------+
1 row in set (0.00 sec)

Se preferisci interrogare sia la data che l’ora nell’output, utilizza la funzione current_timestamp:

SELECT current_timestamp;

Output
+---------------------+
| current_timestamp   |
+---------------------+
| 2022-02-15 19:09:58 |
+---------------------+
1 row in set (0.00 sec)

Puoi utilizzare funzioni di data e ora come queste all’interno di funzioni aritmetiche simili alla sezione precedente. Ad esempio, supponiamo tu voglia sapere quale era la data 11 giorni fa dalla data odierna. In questo caso, potresti utilizzare la stessa struttura di sintassi che hai usato in precedenza per interrogare la funzione current_date e poi sottrarre 11 da essa per trovare la data di undici giorni fa:

SELECT current_date - 11;

Output
+-------------------+
| current_date - 11 |
+-------------------+
|          20220206 |
+-------------------+
1 row in set (0.01 sec)

Come indica questo output, 11 giorni fa dalla current_date (al momento della stesura di questo testo) era il 2022-02-06, o il 6 febbraio 2022. Ora prova a eseguire la stessa operazione, ma sostituisci current_date con la funzione current_time:

SELECT current_time - 11;

Output
+-------------------+
| current_time - 11 |
+-------------------+
|        	233639 |
+-------------------+
1 row in set (0.00 sec)

Questo output mostra che quando sottrai 11 dal valore di current_time, vengono sottratti 11 secondi. L’operazione eseguita in precedenza utilizzando la funzione current_date ha interpretato 11 come giorni, non come secondi. Questa discrepanza nell’interpretazione dei numeri quando si lavora con funzioni di data e ora può essere confusa. Invece di richiederti di manipolare i valori di data e ora utilizzando l’aritmetica in questo modo, molti sistemi di gestione di database ti consentono di essere più esplicito attraverso l’uso di espressioni INTERVAL.

Le espressioni INTERVAL ti consentono di trovare quale sarebbe la data o l’ora prima o dopo un intervallo impostato da una data o un’espressione temporale fornita. Devono avere la seguente forma:

INTERVAL value unit

Ad esempio, per trovare la data tra cinque giorni da ora, potresti eseguire la seguente query:

SELECT current_date + INTERVAL '5' DAY AS "5_days_from_today";

Questo esempio trova il valore current_date e quindi aggiunge l’espressione di intervallo INTERVAL '5' DAY ad esso. Questo restituisce la data tra 5 giorni da adesso:

Output
+-------------------+
| 5_days_from_today |
+-------------------+
| 2022-03-06    	|
+-------------------+
1 row in set (0.00 sec)

Questo è molto meno ambiguo rispetto alla query seguente, che produce un output simile, anche se non identico:

SELECT current_date + 5 AS "5_days_from_today";

Output
+-------------------+
| 5_days_from_today |
+-------------------+
|      	20220306 |
+-------------------+
1 row in set (0.00 sec)

Nota che è anche possibile sottrarre intervalli da date o tempi per trovare valori precedenti rispetto al valore di data specificato:

SELECT current_date - INTERVAL '7' MONTH AS "7_months_ago";

Output
+--------------+
| 7_months_ago |
+--------------+
| 2021-08-01   |
+--------------+
1 row in set (0.00 sec)

Le unità disponibili per l’uso nelle espressioni di INTERVAL dipendono dalla scelta del DBMS, anche se la maggior parte avrà opzioni come HOUR, MINUTE e SECOND:

SELECT current_time + INTERVAL '6' HOUR AS "6_hours_from_now",
current_time - INTERVAL '5' MINUTE AS "5_minutes_ago",
current_time + INTERVAL '20' SECOND AS "20_seconds_from_now";

Output
+------------------+---------------+---------------------+
| 6_hours_from_now | 5_minutes_ago | 20_seconds_from_now |
+------------------+---------------+---------------------+
| 07:51:43     	| 01:46:43  	| 01:52:03.000000 	|
+------------------+---------------+---------------------+
1 row in set (0.00 sec)

Ora che hai imparato le espressioni di intervallo e alcune funzioni di data e ora, continua a praticare il lavoro con i dati di esempio inseriti nel primo passaggio.

Utilizzo di CAST e Funzioni di Aggregazione con Data e Ora

Ricorda dall’esempio tre nella sezione Utilizzo dell’Aritmetica con Date e Orari, quando hai eseguito la seguente query per sottrarre end_time da start_time per calcolare le ore totali che ogni corridore ha completato per gara. L’output, tuttavia, ha prodotto una colonna contenente un output molto lungo, che segue il tipo di dati TIMESTAMP che è stato impostato nella tabella:

SELECT runner_name, race_name, end_time - start_time 
AS total_time 
FROM race_results;

Output
+-------------+---------------+----------------+
| runner_name | race_name     | total_time     |
+-------------+---------------+----------------+
| bolt        | 1600_meters   | 20220918000630 |
| bolt        | 5K            | 20221019002231 |
| bolt        | 10K           | 20221120003805 |
| bolt        | half_marathon | 20221221013904 |
| bolt        | full_marathon | 20230122032310 |
| felix       | 1600_meters   | 20220918000715 |
| felix       | 5K            | 20221019003050 |
| felix       | 10K           | 20221120011017 |
| felix       | half_marathon | 20221221021157 |
| felix       | full_marathon | 20230122040210 |
+-------------+---------------+----------------+
10 rows in set (0.00 sec)

Poiché stai eseguendo un’operazione con due colonne che hanno tipi di dati diversi (end_time che contiene valori TIMESTAMP e start_time che contiene valori TIME), il database non sa quale tipo di dato utilizzare quando stampa il risultato dell’operazione. Invece, converte entrambi i valori in interi in modo da poter eseguire l’operazione, risultando nei numeri lunghi nella colonna total_time.

Per rendere questi dati più chiari da leggere e interpretare, puoi utilizzare la funzione CAST per convertire questi lunghi valori interi nel tipo di dato TIME. Per farlo, inizia con CAST e poi segui immediatamente con una parentesi aperta, i valori che desideri convertire e poi la parola chiave AS e il tipo di dati in cui desideri convertirlo.

La query seguente è identica all’esempio precedente, ma utilizza una funzione CAST per convertire la colonna total_time nel tipo di dati time:

SELECT runner_name, race_name, CAST(end_time - start_time AS time)
AS total_time 
FROM race_results;

Output
+-------------+---------------+------------+
| runner_name | race_name     | total_time |
+-------------+---------------+------------+
| bolt        | 1600_meters   | 00:06:30   |
| bolt        | 5K            | 00:22:31   |
| bolt        | 10K           | 00:38:05   |
| bolt        | half_marathon | 01:39:04   |
| bolt        | full_marathon | 03:23:10   |
| felix       | 1600_meters   | 00:07:15   |
| felix       | 5K            | 00:30:50   |
| felix       | 10K           | 01:10:17   |
| felix       | half_marathon | 02:11:57   |
| felix       | full_marathon | 04:02:10   |
+-------------+---------------+------------+
10 rows in set (0.00 sec)

CAST ha convertito i valori dei dati in TIME in questa uscita, rendendola molto più digeribile da leggere e comprendere.

Ora, utilizziamo alcune funzioni di aggregazione in combinazione con la funzione CAST per trovare i tempi più brevi, più lunghi e totali di ciascun corridore. Prima, eseguiamo una query per il valore minimo (o più breve) del tempo trascorso con la funzione di aggregazione MIN. Di nuovo, è necessario utilizzare CAST per convertire i valori dei dati TIMESTAMP in valori di dati TIME per chiarezza. Si noti che quando si utilizzano due funzioni come in questo esempio, sono richieste due coppie di parentesi e il calcolo delle ore totali (end_time - start_time) dovrebbe essere annidato all’interno di una di esse. Infine, aggiungere una clausola GROUP BY per organizzare questi valori in base alla colonna runner_name in modo che l’output presenti i risultati della gara dei due corridori:

SELECT runner_name, MIN(CAST(end_time - start_time AS time)) AS min_time
FROM race_results GROUP BY runner_name;

Output
+-------------+----------+
| runner_name | min_time |
+-------------+----------+
| bolt        | 00:06:30 |
| felix       | 00:07:15 |
+-------------+----------+
2 rows in set (0.00 sec)

Questo output mostra il tempo più breve di ciascun corridore, in questo caso un minimo di sei minuti e 30 secondi per Bolt e sette minuti e 15 secondi per Felix.

Successivamente, troviamo il tempo più lungo di ciascun corridore. È possibile utilizzare la stessa sintassi della query precedente, ma questa volta sostituire MIN con MAX:

SELECT runner_name, MAX(CAST(end_time - start_time AS time)) AS max_time
FROM race_results GROUP BY runner_name;

Output
+-------------+----------+
| runner_name | max_time |
+-------------+----------+
| bolt        | 03:23:10 |
| felix       | 04:02:10 |
+-------------+----------+
2 rows in set (0.00 sec)

Questo output ci dice che il tempo più lungo di Bolt è stato un totale di tre ore, 23 minuti e 10 secondi; e quello di Felix è stato un totale di quattro ore, due minuti e 10 secondi.

Ora cerchiamo alcune informazioni di alto livello sulle ore totali che ogni corridore ha trascorso correndo. Per questa query, combina la funzione aggregata SUM per trovare la somma totale delle ore basata su end_time - start_time, e usa CAST per convertire quei valori dati in TIME. Non dimenticare di includere GROUP BY per organizzare i valori per i risultati di entrambi i corridori:

SELECT runner_name, SUM(CAST(end_time - start_time AS time))
AS total_hours FROM race_results GROUP BY runner_name;

Output
+-------------+-------------+
| runner_name | total_hours |
+-------------+-------------+
| bolt        |       52880 |
| felix       |       76149 |
+-------------+-------------+
2 rows in set (0.00 sec)

Interessantemente, questo output mostra l’interpretazione per MySQL, che in realtà calcola il tempo totale come interi. Se leggessimo questi risultati come tempo, il tempo totale di Bolt si suddivide in cinque ore, 28 minuti e 80 secondi; e il tempo di Felix si suddivide in sette ore, 61 minuti e 49 secondi. Come puoi vedere, questa scomposizione del tempo non ha senso, il che indica che viene calcolata come un intero e non come tempo. Se provassi questo in un DBMS diverso, come ad esempio PostgreSQL, la stessa query avrebbe un aspetto leggermente diverso:

SELECT runner_name, SUM(CAST(end_time - start_time AS time))
AS total_hours FROM race_results GROUP BY runner_name;

Output
 runner_name | total_hours
-------------+-------------
 felix       | 10:01:44
 bolt        | 06:09:20
(2 rows)

In questo caso, la query in PostgreSQL interpreta i valori come tempo e li calcola come tali, in modo che i risultati di Felix si suddividano in un totale di 10 ore, un minuto e 44 secondi; e quelli di Bolt in sei ore, nove minuti e 20 secondi. Questo è un esempio di come le diverse implementazioni di DBMS possono interpretare i valori dei dati in modo diverso anche se utilizzano la stessa query e lo stesso set di dati.

Conclusion

Capire come utilizzare data e ora in SQL è utile quando si interrogano risultati specifici come minuti, secondi, ore, giorni, mesi, anni; o una combinazione di tutti questi. Inoltre, ci sono molte funzioni disponibili per le date e gli orari che facilitano la ricerca di determinati valori, come la data o l’ora corrente. Mentre questo tutorial ha utilizzato solo l’aritmetica di addizione e sottrazione su date e orari in SQL, è possibile utilizzare valori di data e ora con qualsiasi espressione matematica. Per saperne di più dalla nostra guida su espressioni matematiche e funzioni aggregate e provarle con le tue interrogazioni data e ora.