Funzioni su numeri, date e conversioni

Nella lezione precedente abbiamo visto alcune delle principali funzioni per lavorare con le stringhe nelle query SQL.

MySQL mette a disposizione funzioni specifiche anche per lavorare con numeri e date, oltre a strumenti che permettono di convertire un valore da un tipo di dato a un altro. Queste funzioni sono utili quando dobbiamo effettuare calcoli, usare date o adattare un valore al formato necessario.

Funzioni matematiche

MySQL offre diverse funzioni per eseguire operazioni matematiche sui numeri. Possiamo utilizzarle, ad esempio, per arrotondare un numero, ottenere il suo valore assoluto o recuperare il resto di una divisione.

Vediamo queste funzioni in dettaglio.

Funzione ROUND()

La funzione ROUND() permette di arrotondare un numero, seguendo le regole standard della matematica.

Se non specifichiamo il numero di cifre decimali, il valore viene arrotondato all'intero più vicino.

SELECT ROUND(21.6);

Nell'esempio, il numero 21.6 viene arrotondato a 22.

Possiamo anche specificare il numero di cifre decimali da mantenere, come secondo parametro. Ad esempio, per arrotondare il numero 21.3571 a 21.36, possiamo scrivere:

SELECT ROUND(21.3571, 2);

Possiamo utilizzare ROUND() anche con una colonna:

SELECT prezzo, ROUND(prezzo, 2) AS prezzo_arrotondato
FROM prodotti;

In questo modo il prezzo viene arrotondato a due cifre decimali nel risultato della query.

Funzione TRUNCATE()

TRUNCATE() permette di tagliare le cifre decimali senza arrotondare il numero.

Ad esempio, per tagliare alla prima cifra decimale di 21.3571 in 21.3, possiamo scrivere:

SELECT TRUNCATE(21.3571, 1);

Nell'esempio, 1 indica il numero di cifre decimali da mantenere. A differenza di ROUND(), il valore non viene arrotondato: le cifre successive a quelle indicate vengono semplicemente eliminate.

Funzione FLOOR()

FLOOR() restituisce il più grande numero intero minore o uguale al valore specificato.

Ad esempio, per approssimare in difetto il numero 21.8 a 21, scriviamo:

SELECT FLOOR(21.8);

Con i numeri negativi, FLOOR() arrotonda sempre verso il basso. Ad esempio, -21.8 diventa -22:

SELECT FLOOR(-21.8);

Funzione CEIL()

CEIL() svolge l'operazione opposta di FLOOR(): restituisce il più piccolo numero intero maggiore o uguale al valore specificato.

Ad esempio, per approssimare per eccesso il numero 21.2 a 22, scriviamo:

SELECT CEIL(21.2);

Anche con i numeri negativi, CEIL() approssima al numero più alto. Ad esempio, da -21.2 a -21:

SELECT CEIL(-21.2);

Funzione ABS()

ABS() restituisce il valore assoluto di un numero, eliminando quindi il suo eventuale segno negativo.

Ad esempio, con ABS(-21) viene restituito 21:

SELECT ABS(-21);

La funzione può essere utile, ad esempio, quando siamo interessati alla distanza tra due valori indipendentemente dal loro segno.

Funzione MOD()

MOD() restituisce il resto della divisione tra due numeri.

La sintassi è: MOD(numero,divisore);.

Ad esempio, abbiamo bisogno del resto della divisione di 5 per 2, dove otteniamo 1:

SELECT MOD(5, 2);

Possiamo utilizzare MOD() anche per verificare se un numero intero è pari, usando la scrittura seguente:

SELECT MOD(8, 2);

Se restituisce 0, significa che il numero è pari.

Gestione di date e orari

Le date e gli orari sono molto comuni nei database. Possiamo utilizzarli, ad esempio, per registrare quando un utente si è registrato, quando è stato effettuato un ordine o quanto tempo è trascorso tra due eventi.

MySQL mette a disposizione diverse funzioni per ottenere, modificare e formattare date e orari:

Vediamo queste funzioni in dettaglio.

Funzione NOW()

La funzione NOW() restituisce la data e l'ora attuali.

Di solito, viene restituita la data e l'ora nella forma YYYY-MM-DD HH:MM:SS.

SELECT NOW();

Funzione CURDATE()

CURDATE() restituisce soltanto la data corrente, senza l'orario.

Generalmente, viene usata la sintassi: YYYY-MM-DD.

SELECT CURDATE();

Funzione CURTIME()

CURTIME() restituisce soltanto l'orario corrente, nella forma HH:MM:SS.

Ad esempio:

SELECT CURTIME();

Funzione DATE_ADD()

DATE_ADD() permette di aggiungere un intervallo di tempo a una data.

La sintassi da utilizzare è: DATE_ADD(data, INTERVALquantitàunità); dove unità può essere DAY, MONTH e YEAR.

Ad esempio, per aggiungere 2 mesi ad una data:

SELECT DATE_ADD('2030-01-01', INTERVAL 2 MONTH);

Funzione DATE_SUB()

DATE_SUB() funziona in modo simile a DATE_ADD(), ma permette di sottrarre un intervallo di tempo a una data.

Per esempio, sottraiamo alla data 20 giorni:

SELECT DATE_SUB('2030-01-01', INTERVAL 20 DAY);

Funzione DATEDIFF()

DATEDIFF() permette di calcolare la differenza tra due date. La funzione restituisce sempre il numero di giorni che separano le due date.

Ad esempio:

SELECT DATEDIFF('2040-01-01', '2030-01-01');

Per esempio, possiamo calcolare quanti giorni sono trascorsi dalla data di registrazione di ogni utente:

SELECT nome, DATEDIFF(CURDATE(), data_registrazione) AS giorni_trascorsi
FROM utenti;

Il risultato mostrerà per ogni utente il numero di giorni trascorsi dalla sua registrazione.

Nota: DATEDIFF() considera la differenza in giorni e non tiene conto dell'orario.

Funzione DATE_FORMAT()

DATE_FORMAT() permette di formattare una data o un orario secondo il formato desiderato.

Per scrivere il formato da restituire, è necessario usare dei segnaposti:

Ad esempio, per scrivere la data odierna data nella forma DD/MM/YYYY HH:MM:SS, possiamo scrivere:

SELECT DATE_FORMAT(NOW(), '%d/%m/%Y %H:%i:%s');

Funzione EXTRACT()

EXTRACT() permette di ottenere una singola componente da una data o da un valore temporale.

La sintassi è: EXTRACT(unitàFROMdata); dove unità può essere YEAR, MONTH o DAY.

Per esempio, per ottenere l'anno da una data:

SELECT EXTRACT(YEAR FROM '2030-01-01');

Conversione dei tipi di dati in SQL

In SQL, i dati hanno tipi diversi: possiamo avere numeri, stringhe, date e altri tipi di valore.

In alcune situazioni può essere necessario convertire esplicitamente un valore da un tipo a un altro. Questa operazione viene chiamata casting.

MySQL mette a disposizione, tra le altre, le funzioni CAST() e CONVERT().

Funzione CAST()

La funzione CAST()converte un valore o un'espressione in un altro tipo di dato specificato.

La sintassi è: CAST(espressioneAStipo);.

Per esempio, possiamo convertire una stringa che contiene un numero di telefono in un valore numerico:

SELECT nome, CAST(telefono AS UNSIGNED) AS numero_telefono
FROM utenti;

Puoi convertire un'espressione nei seguenti tipi di dati:

Possiamo utilizzare CAST() con valori provenienti da una colonna. Ad esempio, possiamo convertire i prezzi dei prodotti in numeri decimali:

SELECT CAST(prezzo AS DECIMAL(10,2)) AS prezzo_convertito
FROM prodotti;

Funzione CONVERT()

MySQL permette di effettuare conversioni anche utilizzando CONVERT(). 

La sintassi è: CONVERT(espressione,tipo);

Per esempio, possiamo convertire una stringa in un valore numerico in questo modo:

SELECT CONVERT('123', UNSIGNED);

CONVERT() utilizza gli stessi tipi di valori di conversione che usa anche CAST().

CONVERT() dispone anche di una seconda forma utilizzata per la conversione del set di caratteri:
CONVERT(espressioneUSINGcharset);
Questa forma è diversa dalla conversione del tipo di dato e può essere utilizzata, ad esempio, per convertire una stringa in un determinato character set.