Guida a Data e Ora in MySQL: Tipi di Dati, Funzioni, Formattazione e Query di Intervallo

1. Introduzione

MySQL è un sistema di gestione di database ampiamente utilizzato per la creazione di applicazioni web e database, e gestire correttamente i dati di tipo data è particolarmente importante. Per esempio, i dati relativi alle date compaiono in molti scenari: gestione dei post di un blog, tracciamento della cronologia delle vendite di un prodotto e memorizzazione della cronologia di accesso degli utenti. Gestendo correttamente i dati di tipo data, è possibile elaborare le informazioni in modo efficiente e fornire dati accurati agli utenti.

In questa guida spiegheremo in modo completo tutto, dai tipi di dati data/ora di base e dall’uso delle funzioni data in MySQL, fino ai calcoli basati su date e alle query di intervallo. Questo contenuto è pensato per utenti da principianti a intermedi, e includeremo esempi pratici di query basati su casi d’uso reali—rendendolo utile per il lavoro quotidiano.

Leggendo questo articolo, sarai in grado di fare quanto segue:

• Comprendere le caratteristiche dei tipi di dati data/ora di MySQL e scegliere il tipo più appropriato per i tuoi dati.
• Utilizzare le funzioni data per recuperare e manipolare facilmente la data corrente, le date passate e le date future.
• Estrarre dati per periodi specifici eseguendo ricerche e confronti su intervalli di date.

Ora, diamo un’occhiata ai fondamenti delle date in MySQL a partire dalla sezione successiva.

2. Panoramica dei tipi di dati data/ora di MySQL

Quando si gestiscono le date in MySQL, è importante scegliere il tipo di dato data/ora corretto in base ai dati e al caso d’uso. MySQL fornisce diversi tipi di dato per la gestione di date e orari, ognuno con caratteristiche e utilizzi differenti. In questa sezione spiegheremo in dettaglio i principali tipi di data/ora e i loro tipici casi d’uso.

DATE

Il tipo DATE memorizza solo la data (anno, mese, giorno) e non include una componente di tempo. L’intervallo supportato è da “1000-01-01” a “9999-12-31”, quindi può gestire date dal 1000 al 9999. È adatto per informazioni che richiedono solo la data, dove il tempo non è rilevante, come compleanni o anniversari.

CREATE TABLE users (
    id INT,
    name VARCHAR(50),
    birth_date DATE
);

TIME

Il tipo TIME memorizza l’orario (ore, minuti, secondi). L’intervallo supportato è da “-838:59:59” a “838:59:59”. Poiché può rappresentare tempi negativi, può anche essere usato per esprimere differenze di tempo. È utile per registrare ore di lavoro o durate di attività.

CREATE TABLE work_log (
    id INT,
    task_name VARCHAR(50),
    duration TIME
);

DATETIME

Il tipo DATETIME memorizza sia la data sia l’orario. L’intervallo supportato è da “1000-01-01 00:00:00” a “9999-12-31 23:59:59”. Non dipende dai fusi orari e consente di recuperare il valore memorizzato esattamente come è stato salvato. È adatto per registrare timestamp di eventi passati o programmazioni future.

CREATE TABLE appointments (
    id INT,
    description VARCHAR(50),
    appointment_datetime DATETIME
);

TIMESTAMP

Il tipo TIMESTAMP memorizza data e ora e converte automaticamente i valori in base al fuso orario del server. L’intervallo supportato è da “1970-01-01 00:00:01 UTC” a “2038-01-19 03:14:07 UTC”. È compatibile con il tempo epoch Unix ed è adatto per registrare timestamp e cronologie di modifiche. Inoltre, poiché TIMESTAMP può impostare l’orario corrente come valore predefinito, è comodo per registrare automaticamente i tempi di creazione e aggiornamento.

CREATE TABLE posts (
    id INT,
    title VARCHAR(50),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

YEAR

Il tipo YEAR memorizza solo l’anno. L’intervallo supportato è da “1901” a “2155”. È utile quando si vuole rappresentare un anno specifico, ad esempio l’anno di produzione o l’anno di fondazione.

CREATE TABLE products (
    id INT,
    name VARCHAR(50),
    manufactured_year YEAR
);

Confronto e casi d’uso di ciascun tipo data/ora

Riepilogo

MySQL date/time data types should be chosen based on the characteristics of your data and how you plan to use it. In the next section, we’ll take a closer look at the differences between DATETIME and TIMESTAMP, which are especially easy to confuse.

3. Differences Between DATETIME and TIMESTAMP

When working with dates and times in MySQL, DATETIME and TIMESTAMP are commonly used, but there are important differences between them. Both store year, month, day, hour, minute, and second, but they differ in how they handle time zones and the time range they can store. You should choose based on your use case. In this section, we’ll explain the differences and characteristics of DATETIME and TIMESTAMP in detail.

Characteristics of DATETIME

• Not dependent on time zones : The DATETIME type is not affected by the server time zone setting. You can retrieve the stored value exactly as saved.
• Range : It supports from 1000-01-01 00:00:00 to 9999-12-31 23:59:59.
• Use cases : Suitable for data you want to store without relying on any particular time zone, such as historical events or future schedules.

Example: Storing an event date/time

For example, if you want to keep a “universal” date/time value as-is, DATETIME is a good choice. The following example records an event scheduled at a specific date and time.

CREATE TABLE events (
    id INT,
    event_name VARCHAR(50),
    event_datetime DATETIME
);

In this table, the date/time stored in event_datetime is not affected by time zones, and the value is retrieved exactly as stored.

Characteristics of TIMESTAMP

• Dependent on time zones : The TIMESTAMP type is stored and retrieved based on the server time zone. When moving data between servers in different time zones, conversion will occur accordingly.
• Range : It supports from 1970-01-01 00:00:01 UTC to 2038-01-19 03:14:07 UTC (based on the Unix time range).
• Auto-update support : Using DEFAULT CURRENT_TIMESTAMP and ON UPDATE CURRENT_TIMESTAMP , MySQL can automatically record the current timestamp when inserting or updating data.
• Use cases : Best for automatically tracking creation or update time, such as when you want a timestamp to change whenever a record is updated.

Example: Storing created and updated times for posts

This example uses the auto-update feature of TIMESTAMP to record creation and update times for blog posts.

CREATE TABLE blog_posts (
    id INT,
    title VARCHAR(100),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

With this setting, created_at is recorded when the post is first created, and updated_at is automatically updated every time the post is edited.

Comparison Table: DATETIME vs TIMESTAMP

How to Choose

• Choose DATETIME if you don’t want time zone effects : For example, if you want to store an event time as a fixed local time for a specific country/region, DATETIME can be appropriate.
• Choose TIMESTAMP if you need auto‑update or time zone handling : For example, if you want to automatically record when database rows are updated, TIMESTAMP is convenient.

Summary

DATETIME and TIMESTAMP have different characteristics. By choosing the right one for your purpose, you can manage date/time data more efficiently. In the next section, we’ll look at the essential date functions in MySQL.

4. MySQL Date/Time Functions Basics

MySQL provides many functions for working with dates and times—from retrieving the current date/time to adding or subtracting intervals. These functions are useful for database administration and analysis. In this section, we’ll explain the basics of MySQL date/time functions and their typical use cases.

Functions to Get the Current Date/Time

Tre funzioni comuni per recuperare la data e l’ora corrente in MySQL sono NOW(), CURDATE() e CURTIME().

NOW()

La funzione NOW() restituisce la data e l’ora corrente nel formato YYYY-MM-DD HH:MM:SS. È utile per il logging e per aggiungere timestamp ai record.

SELECT NOW(); -- Get the current date and time

CURDATE()

La funzione CURDATE() restituisce la data corrente nel formato YYYY-MM-DD. Non include l’ora, quindi è adatta quando ti serve solo la data.

SELECT CURDATE(); -- Get the current date

CURTIME()

La funzione CURTIME() restituisce l’ora corrente nel formato HH:MM:SS. Usala quando ti serve solo l’ora senza la data.

SELECT CURTIME(); -- Get the current time

Functions to Add/Subtract Dates

Per aggiungere o sottrarre intervalli a una data, usa DATE_ADD() e DATE_SUB(). Queste funzioni semplificano il calcolo di date future o passate.

DATE_ADD()

La funzione DATE_ADD() aggiunge un intervallo specificato a una data. Per esempio, è utile per ottenere la data 7 giorni dopo o 1 mese dopo.

SELECT DATE_ADD('2023-01-01', INTERVAL 7 DAY); -- Returns 2023-01-08

DATE_SUB()

La funzione DATE_SUB() sottrae un intervallo specificato da una data. Usala per calcolare date passate.

SELECT DATE_SUB('2023-01-01', INTERVAL 1 MONTH); -- Returns 2022-12-01

Function to Calculate Date Differences

Per calcolare la differenza tra due date, DATEDIFF() è comodo. Per esempio, puoi calcolare quanti giorni sono passati da una data specifica, o il numero di giorni tra due date.

DATEDIFF()

La funzione DATEDIFF() restituisce la differenza tra due date in giorni. È utile quando vuoi confermare il numero di giorni da una data di inizio a una data di fine.

SELECT DATEDIFF('2023-01-10', '2023-01-01'); -- Returns 9

Other Useful Date Functions

MySQL fornisce ulteriori funzioni utili per le date.

EXTRACT()

La funzione EXTRACT() estrae una parte specifica (anno, mese, giorno, ecc.) da una data. È utile quando ti serve solo una porzione di una data.

SELECT EXTRACT(YEAR FROM '2023-01-01'); -- Extract year (2023)
SELECT EXTRACT(MONTH FROM '2023-01-01'); -- Extract month (1)
SELECT EXTRACT(DAY FROM '2023-01-01'); -- Extract day (1)

DATE_FORMAT()

La funzione DATE_FORMAT() visualizza una data in un formato specificato. È utile quando vuoi mostrare le date in un formato particolare (ad esempio, la formattazione in stile giapponese).

SELECT DATE_FORMAT('2023-01-01', '%Y-%m-%d'); -- Returns 2023-01-01

5. Date Formatting and Conversion Methods

In MySQL, puoi modificare il modo in cui le date vengono visualizzate in un formato più leggibile, o convertire stringhe di data in tipi data. Questo ti consente di controllare in modo flessibile i formati di visualizzazione e gestire i dati in modo coerente anche quando gli input provengono da formati diversi. In questa sezione, spiegheremo le funzioni principali usate per la formattazione e la conversione delle date.

Date Formatting with the DATE_FORMAT() Function

Utilizzando DATE_FORMAT(), puoi visualizzare i valori di data in un formato specificato. È particolarmente utile quando ti serve un formato di visualizzazione diverso da quello standard, come il formato giapponese “YYYY年MM月DD日”.

Format Options

Con DATE_FORMAT(), puoi usare le seguenti opzioni di formato:

• %Y : anno a 4 cifre
• %y : anno a 2 cifre
• %m : mese a 2 cifre (01–12)
• %d : giorno a 2 cifre (01–31)
• %H : ora a 2 cifre (00–23)
• %i : minuto a 2 cifre (00–59)
• %s : secondo a 2 cifre (00–59)

Example Usage

Ad esempio, per visualizzare la data 2023-01-01 come “2023年01月01日”, puoi scrivere quanto segue:

SELECT DATE_FORMAT('2023-01-01', '%Y年%m月%d日'); -- Returns 2023年01月01日

Puoi anche visualizzare una data e ora con le barre, ad esempio “2023/01/01 12:30:45.”

SELECT DATE_FORMAT('2023-01-01 12:30:45', '%Y/%m/%d %H:%i:%s'); -- Returns 2023/01/01 12:30:45

Conversione di stringhe in date con STR_TO_DATE()

La funzione STR_TO_DATE() converte una stringa di data in un tipo data MySQL. Per esempio, è utile quando si desidera trattare una stringa come “2023年01月01日” come valore DATE.

Esempio di utilizzo

Per convertire la stringa “2023-01-01” in un valore DATE, scrivi quanto segue:

SELECT STR_TO_DATE('2023-01-01', '%Y-%m-%d'); -- Returns 2023-01-01 as a DATE

Per convertire una stringa in stile giapponese come “2023年01月01日” in una data, specifica il formato corrispondente:

SELECT STR_TO_DATE('2023年01月01日', '%Y年%m月%d日'); -- Returns 2023-01-01 as a DATE

Esempi di conversione di date da formati diversi

Nell’uso reale, i formati di input delle date possono variare, quindi potresti dover convertire da formati diversi. Di seguito alcuni esempi.

1. Converti “YYYY/MM/DD” in un valore DATE

   SELECT STR_TO_DATE('2023/01/01', '%Y/%m/%d'); -- Returns 2023-01-01 as a DATE
   

2. Converti “MM-DD-YYYY” in un valore DATE

   SELECT STR_TO_DATE('01-01-2023', '%m-%d-%Y'); -- Returns 2023-01-01 as a DATE
   

In questo modo, anche se i dati vengono inseriti in formati diversi, puoi archiviarli e gestirli in modo coerente in un formato data unificato.

Differenze tra DATE_FORMAT e STR_TO_DATE, e quando usarli

• DATE_FORMAT() : Utilizzata per cambiare il formato di visualizzazione dei valori data esistenti. Poiché la modifica influisce solo sulla visualizzazione, i dati memorizzati non vengono modificati.
• STR_TO_DATE() : Utilizzata per convertire una stringa di data in un valore MySQL DATE o DATETIME. Questo cambia il tipo di dato memorizzato e facilita l’elaborazione delle date con altre funzioni e query MySQL.

Riepilogo

Utilizzando DATE_FORMAT() e STR_TO_DATE(), puoi gestire in modo flessibile come i dati di data vengono visualizzati e memorizzati. Questo consente di gestire gli input degli utenti o del sistema in modo più flessibile, mantenendo una gestione coerente delle date. Nella sezione successiva, spiegheremo i calcoli e i confronti di date e approfondiremo come calcolare e confrontare periodi usando i dati di data.

6. Calcoli e confronti di date

MySQL fornisce diverse funzioni pratiche per i calcoli e i confronti di date. Queste permettono di estrarre dati per periodi specifici o di calcolare differenze di data per analisi. In questa sezione, presenteremo le funzioni principali usate per i calcoli e i confronti di date, insieme a esempi di utilizzo.

Funzione per calcolare differenze di data: DATEDIFF()

La funzione DATEDIFF() restituisce la differenza tra due date in “giorni”. È utile quando vuoi verificare i giorni trascorsi tra date o determinare quanti giorni sono passati da un evento passato fino a oggi.

Esempio di utilizzo

Ad esempio, per calcolare il numero di giorni dal 1 gennaio 2023 al 10 gennaio 2023, scrivi quanto segue:

SELECT DATEDIFF('2023-01-10', '2023-01-01'); -- Returns 9

In questo esempio, la differenza dalla data di inizio a quella di fine è di 9 giorni, quindi il risultato è 9.

Calcolare differenze per unità con TIMESTAMPDIFF()

La funzione TIMESTAMPDIFF() calcola la differenza tra due date o datetime in un’unità specificata (anno, mese, giorno, ora, minuto, secondo). Per esempio, puoi calcolare differenze in “mesi” o “ore” a seconda delle necessità.

Esempio di utilizzo

1. Calcola una differenza in mesi

   SELECT TIMESTAMPDIFF(MONTH, '2022-01-01', '2023-01-01'); -- Returns 12
   

2. Calcola una differenza in ore

   SELECT TIMESTAMPDIFF(HOUR, '2023-01-01 00:00:00', '2023-01-02 12:00:00'); -- Returns 36
   

Confrontare le date

I confronti di data vengono eseguiti usando gli operatori di confronto standard di MySQL (<, >, <=, >=, =). Questo consente di estrarre dati entro un periodo specifico o includere date passate/future come condizioni.

Esempio di utilizzo

Ad esempio, per estrarre dati “dal 1 gennaio 2023 in poi”, scrivi quanto segue:

SELECT * FROM events WHERE event_date >= '2023-01-01';

Specificare un intervallo con BETWEEN

L’operatore BETWEEN consente di specificare un intervallo di date per recuperare i dati. È utile quando si desidera estrarre i record che rientrano in un periodo specifico.

Example Usage

Ad esempio, per recuperare i dati dal 1 gennaio 2023 al 31 gennaio 2023, scrivi quanto segue:

SELECT * FROM events WHERE event_date BETWEEN '2023-01-01' AND '2023-01-31';

Calcoli di data con ADDDATE() e SUBDATE()

È possibile utilizzare ADDDATE() e SUBDATE() per aggiungere o sottrarre un numero di giorni a una data. Queste funzioni sono comode per calcolare date future o passate.

Example Usage

1. Aggiungi 10 giorni a una data

   SELECT ADDDATE('2023-01-01', 10); -- Returns 2023-01-11
   

2. Sottrai 10 giorni da una data

   SELECT SUBDATE('2023-01-01', 10); -- Returns 2022-12-22
   

Riepilogo

Utilizzando le funzionalità di calcolo e confronto delle date di MySQL, è possibile estrarre in modo efficiente i dati per periodi specifici e analizzare i dati per intervalli temporali. Nella sezione successiva, esamineremo più da vicino l’argomento avanzato delle “ricerche per intervallo di date”.