Tag: sql

MySQL – operazioni con date, tempo e intervalli – prima parte

In MySQL sono disponibili principalmente cinque tipi di dato per gestire le informazioni temporali: DATE, TIME, DATETIME, TIMESTAMP e YEAR.

Ognuno di questi tipi ha un range di valori validi. MySQL gestisce questi dati in un formato specifico, ma accetta una varietà di formati in ingresso che è in grado di interpretare e convertire correttamente. In alternativa, vengono fornite specifiche funzioni per la conversione dei dati, come ad esempio STR_TO_DATE().

Il formato di questi tipi di dato è il seguente:

TIPOFORMATORANGE
DATETIMEYYYY-MM-DD hh:mm:ss[.fraction]da '1000-01-01 00:00:00' a '9999-12-31 23:59:59'
DATEYYYY-MM-DDda '1000-01-01' a '9999-12-31'
TIMEhh:mm:ss[.fraction]da '-838:59:59.000000' a'838:59:59.000000'
YEARYYYYda 1901 a 2155
TIMESTAMPYYYY-MM-DD hh:mm:ss[.fraction]da '1970-01-01 00:00:01' UTC a '2038-01-19 03:14:07' UTC

Il formato TIMESTAMP memorizza il dato in formato “unix type”: il tempo viene rappresentato come il numero di secondi trascorsi dalla mezzanotte del 01 gennaio 1970 (epoch) UTC. A seconda della versione del DBMS, è disponibile una risoluzione in microsecondi del timestamp.

Questa rappresentazione offre il vantaggio di gestire la data come numero intero e in modo indipendente dai fusi orari. MySQL ha funzioni dirette per convertire il formato.

Usare TIMESTAMP per data creazione e modifica del record

Il tipo TIMESTAMP si rileva utile per memorizzare in specifiche colonne la data di creazione di un record e la data dell’ultima modifica in maniera automatica. Per fare questo è possibile definire due colonne di questo tipo:

create table test
(
    ...
    date_created timestamp default current_timestamp() not null,
    last_updated timestamp default current_timestamp() not null on update current_timestamp()
);

In questo modo, ogni volta che verrà aggiunto un record alla tabella i campi date_created e last_updated verranno valorizzati con il timestamp corrente. In caso di modifica del record il campo last_updated verrà automaticamente aggiornata con il timestamp della modifica.

SQL – select record dell’anno passato

Immaginiamo una tabella con un campo datetime. Se volessimo filtrare la nostra query select per i record dell’anno passato possiamo procedere nel seguente modo:

select * from table t where YEAR(t.date) = YEAR(current_date - INTERVAL 1 YEAR);

Abbiamo utilizzato le seguenti funzioni:

  • YEAR(dt) questa funzione restituisce un intero rappresentante l’anno del paremetro date o datetime dt
  • CURRENT_DATE() questa funzione restituisce la data corrente nel formato 'YYYY-MM-DD'
  • INTERVAL questa funzione meriterebbe un articolo intero. È usata per effettuare calcoli con DATE e TIME. La sintassi è la seguente: INTERVAL expr unit

Naturalmente, modificando il valore dell’espressione INTERVAL 1 YEAR è possibile selezionare anche record con anni precedenti.

NB: è possibile anche fare a meno di INTERVAL e usare una semplice sottrazione:

select * from table t where YEAR(t.date) = YEAR(current_date)-1;

ma volevo approfittarne per introdurre l’espressione INTERVAL di cui parlerò con maggiori dettagli in un prossimo articolo.

SQL – selezionare record dell’anno corrente

Immaginiamo una tabella con un campo datetime. Se volessimo filtrare la nostra query select per i record dell’anno corrente possiamo procedere nel seguente modo:

select * from table t where YEAR(t.date) = YEAR(current_date);

Abbiamo utilizzato le seguenti funzioni:

  • YEAR(dt) questa funzione restituisce un intero rappresentante l’anno del paremetro date o datetime dt
  • CURRENT_DATE() questa funzione restituisce la data corrente nel formato 'YYYY-MM-DD'