Gestione delle Date in MariaDB

Nel mio lavoro di sviluppatore full-stack professionale, sono spesso in qualche modo coinvolto nella creazione o manutenzione di un database. Data l’importanza di un asset così delicato e quella di avere una governance completa dei dati, la mia scelta ricade inevitabilmente in progetti open source e, soprattutto a lavoro, la mia predilezione va a MariaDB per la gestione dei database dei clienti.

Un tipo di dato fondamentale nei database, che si ripete spesso in tabelle differenti e in diverse rappresentazioni, è quello delle date e del tempo. Di seguito facciamo un breve riassunto dei tipi di dato che rappresentano una data in MariaDB:

DATE che rappresenta le date in formato YYYY-MM-DD, anche se permette di assegnare valori anche in altri formati, tra cui stringhe e interi.
DATETIME(<microseconds>) come sopra, ma rappresenta sia la data che l’ora in formato YYYY-MM-DD HH:MM:SS.ffffff, la precisione dei microsecondi varia da 0 (default, se non specificato) a 6.
SQL_TSI_YEAR alias del tipo YEAR.
TIME(<microseconds>) che rappresenta il tempo in formato HH:MM:SS.ffffff che va da -838:59:59.999999 a 838:59:59.999999, la precisione dei microsecondi varia da 0 (default, se non specificato) a 6.
TIMESTAMP(<microseconds>) tipicamente usato per definire in quale momento è stato creato o modificato un record e il comportamento di default è quello di impostarlo al secondo in cui è stata effettuata la modifica. MariaDB può salvare valori che partono dal 1970-01-01 00:00:00 fino al 2106-02-07 06:28:15 UTC.
YEAR che rappresenta gli anni, come interi con quattro cifre, che vanno dal 1901 al 2155.

Best practice

Quando mi trovo ad avere a che fare con le date, ci sono due situazioni distinte di cui devo occuparmi: quando la data è obbligatoria, in tal caso è necessario mettere una data, anche solo il timestamp attuale; oppure quando la data non è obbligatoria, in cui potremmo ritrovarci a non avere nessun timestamp di riferimento.

Se dobbiamo salvare l’istante in cui è stato creato un record, ad esempio in una colonna created_at normalmente possiamo permetterci di impostare il campo NOT NULL, in quanto sicuramente esiste un istante che possiamo salvare. Inoltre questo può essere fornito direttamente dal DBMS e, se impostato, può essere salvato in automatico.

Ci sono situazioni in cui però non abbiamo necessariamente un valore da inserire, come ad esempio per una colonna di modifica modified_at. In questo caso è opportuno inserire come contenuto della cella il valore NULL, per evitare di creare confusione tra valori verosimili e campi mancanti del tutto.

Trappola mortale

Nonostante le date di default partano dall’anno 1000 (ad eccezione di TIMESTAMP che parte da 1970), alcuni sistemi legacy o retrocompatibili potrebbero impostare tutte le colonne delle date a NOT NULL e mettere come default un valore falsy 0000-00-00 che, quando viene convertito in una variabile, potrebbe restituire NULL programmaticamente nelle strutture di controllo.

Questo è un comportamento a cui bisogna fare molta attenzione, in quanto potrebbe impedire di trasferire alcuni dati da questi database ad altri più moderni, come è successo a me di recente mentre cercavo di fare un intervento di manutenzione.

I motivi per cui è possibile inserire questi valori sono due: la versione del database è effettivamente datata e non era implementato il controllo sui dati; oppure è stato impostato un flag per rendere i controlli meno rigorosi, impostando la sql_mode.

Trasferire i dati resta comunque possibile, sempre grazie a sql_mode, che possiamo sfruttarlo a nostra volta per rendere i controlli meno severi quando trasferiamo i record. In particolare è possibile disattivare i flag NO_ZERO_DATE e NO_ZERO_IN_DATE, che sono proprio i controlli che tolgono la possibilità di avere date a zero o che contengono zeri nei mesi o nei giorni.

Questa variabile è impostabile sia a livello globale come variabile di sistema, che come variabile di sessione che vale per tutta la durata della connessione attiva:

SET SESSION sql_mode = '';

A questo punto possiamo muovere liberamente tutti i record legacy e, auspico, anche modificarli a NULL facendo attenzione a non alterare la semantica dei dati.