MySQL DATETIME spiegato: tipo di dato, intervallo e migliori pratiche

1. Cos’è il DATETIME di MySQL?

Il DATETIME di MySQL è un tipo di dato usato per memorizzare data e ora in un unico campo. Gestire valori di data e ora in un database è essenziale per molte applicazioni, come i sistemi di logging e di prenotazione. Il tipo DATETIME memorizza data e ora insieme e supporta un ampio intervallo di valori. Il suo intervallo va da '1000-01-01 00:00:00' a '9999-12-31 23:59:59', e supporta anche i secondi frazionari.

2. Panoramica dei tipi di dato data e ora di MySQL

2.1 Tipi di dato per gestire date e ore

MySQL fornisce i seguenti tipi di dato per lavorare con date e ore:

• DATE : Memorizza una data (anno, mese, giorno). L’intervallo è '1000-01-01' a '9999-12-31' .
• TIME : Memorizza solo l’ora. L’intervallo è '-838:59:59' a '838:59:59' .
• DATETIME : Memorizza sia data che ora. L’intervallo è '1000-01-01 00:00:00' a '9999-12-31 23:59:59' .
• TIMESTAMP : Memorizza un timestamp UNIX. L’intervallo è '1970-01-01 00:00:01' a '2038-01-19 03:14:07' .

2.2 Differenze tra DATETIME e TIMESTAMP

DATETIME e TIMESTAMP sono simili, ma presentano le seguenti differenze:

• Fuso orario : DATETIME memorizza un valore fisso che non dipende dai fusi orari. Al contrario, TIMESTAMP viene convertito in UTC al momento della memorizzazione e riconvertito nel fuso orario corrente del server al momento del recupero. Per questo motivo, DATETIME è adatto a valori data/ora indipendenti dal fuso (ad esempio, orari di eventi), mentre TIMESTAMP è indicato per dati legati al fuso orario del server, come i log.
• Formato di memorizzazione : DATETIME è memorizzato così com’è, mentre TIMESTAMP è memorizzato come timestamp UNIX. Di conseguenza, i valori TIMESTAMP sono influenzati dalle impostazioni del fuso orario del server quando rappresentano l’ora.

3. Come usare DATETIME in MySQL

3.1 Creare una colonna DATETIME

Per creare una colonna con il tipo DATETIME, usa la seguente sintassi SQL:

CREATE TABLE sample_table (
    event_time DATETIME
);

In questo esempio, viene creata una colonna DATETIME chiamata event_time in una tabella denominata sample_table.

3.2 Inserire valori DATETIME

I valori DATETIME di MySQL possono essere inseriti in vari formati. Il formato base è 'YYYY-MM-DD HH:MM:SS'. Per esempio:

INSERT INTO sample_table (event_time) VALUES ('2024-09-16 14:30:00');

Sono consentiti anche i seguenti formati:

• 'YY-MM-DD HH:MM:SS' : Un formato che utilizza un anno a 2 cifre.
• 'YYYYMMDDHHMMSS' : Un formato senza separatori.

Esempio:

INSERT INTO sample_table (event_time) VALUES ('24-09-16 14:30:00');
INSERT INTO sample_table (event_time) VALUES (20240916143000);

I dati inseriti in questi formati verranno memorizzati correttamente. Se l’anno è specificato con due cifre, '70-99' viene convertito in 1970-1999, e '00-69' in 2000-2069.

3.3 Recuperare valori DATETIME

Quando si recuperano valori DATETIME, MySQL li visualizza nel formato predefinito 'YYYY-MM-DD HH:MM:SS'. Per esempio:

SELECT event_time FROM sample_table;

Questa query mostra i valori nella colonna DATETIME usando il formato standard.

4. Lavorare con i secondi frazionari

4.1 Precisione del DATETIME

In MySQL, i valori DATETIME possono includere secondi frazionari. È possibile specificare la precisione usando l’opzione fsp, che consente di memorizzare secondi frazionari da 0 a 6 cifre. Per esempio, per creare una colonna con 3 cifre di secondi frazionari:

CREATE TABLE precise_times (
    event_time DATETIME(3)
);

In questo esempio, la colonna event_time può memorizzare secondi frazionari fino a 3 cifre.

4.2 Inserire valori con secondi frazionari

Per inserire un valore DATETIME che includa secondi frazionari, usa il seguente:

INSERT INTO precise_times (event_time) VALUES ('2024-09-16 14:30:00.123');

Questa query memorizza il valore includendo i secondi frazionari in modo accurato. La parte frazionaria viene memorizzata senza essere troncata e la precisione viene preservata al momento del recupero del valore.

5. Best Practices for DATETIME

5.1 Choosing Between DATETIME and TIMESTAMP

• When to use DATETIME : Per valori di data/ora fissi che non dipendono dai fusi orari (ad esempio, orari di inizio di un evento o date di prenotazione).
• When to use TIMESTAMP : Per dati di data/ora legati al fuso orario del server (ad esempio, timestamp di creazione o aggiornamento di un record).

5.2 Time Zone Management

Poiché DATETIME non memorizza informazioni sul fuso orario, l’applicazione deve gestire i fusi orari separatamente. D’altro canto, TIMESTAMP tiene automaticamente conto del fuso orario del server durante la memorizzazione e il recupero dei valori, rendendolo adatto a sistemi che operano in diversi fusi orari a livello globale.

6. Common Mistakes and How to Avoid Them

6.1 Zero Dates and Invalid Values

In MySQL, se si tenta di inserire un valore DATETIME non valido, potrebbe essere memorizzata una data zero come '0000-00-00 00:00:00'. Poiché questa non è generalmente una data valida, è necessario convalidare i dati di input per impedire l’inserimento di valori non validi. Implementare una validazione che garantisca che l’input rispetti l’intervallo e il formato corretti può aiutare a prevenire la memorizzazione di date zero.

6.2 Misusing Precision

Quando si specifica la precisione dei secondi frazionari, usare una precisione errata può produrre risultati inattesi. Impostare la precisione dei secondi frazionari solo quando necessario e scegliere il valore fsp con attenzione. Per esempio, se la tua applicazione non richiede precisione sub‑secondo, non è necessario aggiungere secondi frazionari a una colonna DATETIME.

7. Summary

In questo articolo abbiamo spiegato in dettaglio il tipo DATETIME di MySQL. DATETIME è un tipo di dato molto utile per memorizzare sia data che ora, ed è adatto quando è necessario conservare valori che non devono essere influenzati dai fusi orari. Comprendendo le differenze tra DATETIME e TIMESTAMP, il funzionamento dei fusi orari e l’uso dei secondi frazionari, è possibile gestire i dati di data/ora in modo più efficace nel proprio database. Inoltre, conoscere gli errori comuni e come evitarli aiuta a mantenere la coerenza e l’affidabilità dei dati.

8. Frequently Asked Questions (FAQ)

Q1: What is the main difference between DATETIME and TIMESTAMP?

DATETIME memorizza una data e ora fisse che non dipendono dai fusi orari. Per esempio, è adatto per memorizzare date di prenotazione o orari di eventi che devono rimanere gli stessi in qualsiasi fuso orario. Al contrario, TIMESTAMP è memorizzato in UTC e convertito nel fuso orario del server al momento del recupero. È adatto per dati di data/ora che dipendono dal fuso orario del server, come i log.

Q2: How can I store fractional seconds with DATETIME?

È possibile impostare la precisione dei secondi frazionari specificando un valore fsp quando si crea la colonna DATETIME. Per esempio, DATETIME(3) memorizza i secondi frazionari fino a 3 cifre. Utilizzando un valore formattato correttamente includendo i secondi frazionari durante l’inserimento, questi verranno memorizzati correttamente.

Q3: Should I use DATETIME or TIMESTAMP?

Dipende dal caso d’uso. Usa DATETIME quando desideri memorizzare una data e ora fisse. Usa TIMESTAMP per dati di data/ora che sono influenzati dal fuso orario del server, come i tempi di creazione o aggiornamento di un record. Poiché TIMESTAMP esegue automaticamente la conversione del fuso orario, è adatto quando il tuo sistema deve operare in diversi fusi orari.