1. Cos’è il DATETIME di MySQL?
Il DATETIME di MySQL è un tipo di dato progettato per gestire simultaneamente valori di data e ora. Gestire data e ora in un database è fondamentale per varie applicazioni, come sistemi di logging o di prenotazione. Il tipo DATETIME memorizza sia la data sia l’ora in un unico campo e può contenere un ampio intervallo di valori, da '1000-01-01 00:00:00' a '9999-12-31 23:59:59', supportando anche i secondi frazionari.
2. Panoramica dei tipi di dato data e ora di MySQL
2.1 Tipi di dato per la gestione di date e ore
MySQL fornisce i seguenti tipi di dato per la gestione di date e ore:
DATE: Un tipo di dato per gestire le date (anno, mese, giorno). L’intervallo va da'1000-01-01'a'9999-12-31'.TIME: Un tipo di dato per gestire solo l’ora. L’intervallo va da'-838:59:59'a'838:59:59'.DATETIME: Un tipo di dato che combina data e ora. L’intervallo va da'1000-01-01 00:00:00'a'9999-12-31 23:59:59'.TIMESTAMP: Un tipo di dato per memorizzare timestamp UNIX. L’intervallo va da'1970-01-01 00:00:01'a'2038-01-19 03:14:07'.
2.2 Differenze tra DATETIME e TIMESTAMP
Sebbene DATETIME e TIMESTAMP siano simili, presentano le seguenti differenze fondamentali:
- Fuso orario :
DATETIMEmemorizza un valore fisso indipendente dal fuso orario. Al contrario, i valoriTIMESTAMPvengono convertiti in UTC al momento della memorizzazione e poi riconvertiti nel fuso orario corrente del server al momento del recupero. Pertanto,DATETIMEè adatto per date e ore non influenzate dai fusi orari (ad es. orari di eventi), mentreTIMESTAMPè più indicato per dati legati al fuso orario del server, come i record di log. - Formato di memorizzazione :
DATETIMEè memorizzato nella sua rappresentazione esatta, mentreTIMESTAMPè memorizzato come timestamp UNIX. Di conseguenza, la rappresentazione temporale diTIMESTAMPè influenzata dalle impostazioni del fuso orario del server.
3. Come utilizzare DATETIME in MySQL
3.1 Creare una colonna DATETIME
Per creare una colonna di tipo DATETIME, utilizza la seguente sintassi SQL:
CREATE TABLE sample_table (
event_time DATETIME
);
In questo esempio, stiamo creando 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');
Altri formati consentiti includono:
'YY-MM-DD HH:MM:SS': Un formato che specifica l’anno con due cifre.'YYYYMMDDHHMMSS': Un formato che specifica i valori senza delimitatori.
Esempi:
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 salvati correttamente. Se l’anno è specificato con due cifre, '70-99' verrà convertito in 1970-1999, e '00-69' verrà convertito 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 mostrerà i valori della colonna DATETIME nella tua tabella nel formato standard.
4. Gestione dei secondi frazionari
4.1 Precisione del DATETIME
MySQL consente di includere secondi frazionari nei valori DATETIME. È possibile specificare la precisione usando l’opzione fsp, che permette di memorizzare secondi frazionari con una precisione compresa tra 0 e 6. Per esempio, per creare una colonna con tre cifre decimali per i secondi frazionari:
CREATE TABLE precise_times (
event_time DATETIME(3)
);
In questo esempio, la colonna event_time può memorizzare fino a tre cifre di secondi frazionari.
4.2 Inserire valori con secondi frazionari
Per inserire un valore DATETIME che includa secondi frazionari, utilizza il seguente:
INSERT INTO precise_times (event_time) VALUES ('2024-09-16 14:30:00.123');
Questa query memorizza accuratamente i valori includendo i secondi frazionari. La parte frazionaria inserita viene memorizzata senza troncamento e la sua precisione viene mantenuta al recupero.
5. Best Practices for DATETIME
5.1 Quando usare DATETIME vs. TIMESTAMP
- Usa
DATETIMEquando : memorizzare date e orari fissi indipendenti dai fusi orari (ad es. orari di inizio di un evento, date di prenotazione). - Usa
TIMESTAMPquando : memorizzare dati di data e ora legati al fuso orario del server (ad es. orari di creazione o aggiornamento dei dati).
5.2 Gestione dei fusi orari
Poiché DATETIME non ha un concetto di fuso orario, la gestione del fuso orario deve essere gestita dall’applicazione. D’altra parte, TIMESTAMP tiene automaticamente conto del fuso orario del server durante la memorizzazione e il recupero dei valori, rendendolo adatto a operazioni tra diversi fusi orari a livello globale.
6. Errori comuni e come evitarli
6.1 Date zero e valori non validi
In MySQL, tentare di inserire un valore DATETIME non valido causerà la memorizzazione di una “data zero” '0000-00-00 00:00:00'. Poiché questa non è generalmente una data valida, è fondamentale convalidare l’input per evitare l’inserimento di valori non validi. Implementare una convalida che assicuri che i dati di input rispettino gli intervalli e i formati appropriati può prevenire la memorizzazione di date zero.
6.2 Uso improprio della precisione
Quando si specifica la precisione dei secondi frazionari, usare una precisione errata può portare a risultati indesiderati. Specificare la precisione dei secondi frazionari solo quando necessario e impostare attentamente il valore fsp. Ad esempio, se la tua applicazione non richiede precisione sub-secondaria, non è necessario impostare i secondi frazionari per la colonna DATETIME.
7. Conclusione
Questo articolo ha fornito una spiegazione dettagliata del tipo di dato DATETIME di MySQL. DATETIME è un tipo di dato molto utile per gestire simultaneamente data e ora, adatto a memorizzare valori non influenzati dai fusi orari. Comprendendo le differenze tra DATETIME e TIMESTAMP, la gestione dei fusi orari e l’uso dei secondi frazionari, è possibile gestire efficacemente i dati di data e ora nel proprio database. Inoltre, la conoscenza degli errori comuni e delle relative strategie di evitamento aiuterà a mantenere la coerenza e l’affidabilità dei dati.
8. Domande frequenti (FAQ)
Q1: Quali sono le principali differenze tra DATETIME e TIMESTAMP?
DATETIME memorizza una data e un’ora fisse indipendenti dai fusi orari. Per esempio, è adatto a memorizzare date di prenotazione o orari di eventi che rimangono coerenti in tutti i fusi orari. Al contrario, TIMESTAMP è memorizzato in base a UTC e convertito nel fuso orario del server al recupero. È adatto a dati sensibili al tempo, come i log, che dipendono dal fuso orario del server.
Q2: Come posso memorizzare i secondi frazionari con DATETIME?
Quando crei una colonna DATETIME, puoi impostare la precisione per i secondi frazionari specificando il valore fsp. Ad esempio, specificare DATETIME(3) consente di memorizzare secondi frazionari fino a tre cifre decimali. Quando inserisci, usa un valore che includa i secondi frazionari e verrà memorizzato nel formato appropriato.
Q3: Devo usare DATETIME o TIMESTAMP?
Dipende dal caso d’uso. Usa DATETIME se desideri memorizzare una data e un’ora fisse. D’altra parte, usa TIMESTAMP per dati di data e ora influenzati dal fuso orario del server, come le date di creazione o aggiornamento dei dati. TIMESTAMP è adatto quando è necessaria la conversione automatica del fuso orario, soprattutto per operazioni tra diversi fusi orari.
