Funzioni SQL per la data e l'ora in 5 dialetti SQL più diffusi

Siete confusi da tutte le funzioni di data e ora utilizzate nei diversi dialetti SQL? In questo articolo riassumo i tipi di dati di data e ora utilizzati in PostgreSQL, Oracle, SQLite, MySQL e T-SQL. Fornisco inoltre esempi delle principali funzioni SQL per la data e l'ora utilizzate in questi dialetti. È ora di diventare dei guru della data e dell'ora!

Volete calcolare quanto spesso i dipendenti arrivano in ritardo al lavoro? O quanto tempo ci vuole per completare un ordine? Sono molti i casi in cui gli analisti di dati hanno bisogno di eseguire calcoli su valori relativi alla data e all'ora in SQL. Tuttavia, i tipi di dati e le funzioni di data e ora differiscono in modo significativo tra i vari dialetti di SQL. Le query avranno un aspetto molto diverso a seconda che vengano scritte, ad esempio, in PostgreSQL o in SQL Server.

In questo articolo, affronterò le basi della gestione di date e orari in diversi dialetti di SQL. Fornisco anche esempi di funzioni chiave per la data e l'ora.

Se volete saltare a una determinata parte, ecco i dialetti SQL trattati in questo articolo:

• PostgreSQL
• Oracle
• SQLite
• MySQL
• T-SQL (MS SQL Server)

Potete anche iniziare a imparare i tipi di dati di data e ora oggi stesso con il corso Tipi di dati in SQL. Questo corso interattivo copre i tipi di dati che funzionano in tutti i più diffusi sistemi di gestione di database relazionali, tra cui SQL Server, MySQL, Oracle e PostgreSQL.

Funzioni SQL di data e ora in diversi dialetti

È sempre più facile ottenere nuove informazioni attraverso casi d'uso reali. Per capire come le date e gli orari possono essere elaborati in diversi dialetti SQL, suggerisco di utilizzare la seguente tabella che mostra i viaggi previsti per il 2022. Qui abbiamo sia le date che i timestamp con un offset di fuso orario.

Vediamo ora come trattare questi dati in diversi dialetti SQL.

Funzioni di data e ora di PostgreSQL

In PostgreSQL esistono quattro tipi di dati che elaborano date e orari:

• Il tipo di dati date è utilizzato per memorizzare date senza l'ora esatta (ad esempio '2022-21-03').
• Il tipo di dati time consente di memorizzare l'ora senza la data (ad es. '8:34:59'). Per impostazione predefinita, il tipo di dati time non include informazioni sul fuso orario. Se si desidera specificare il fuso orario, è necessario digitare use time with time zone. Tuttavia, si raccomanda di utilizzare il tipo di dati successivo quando si ha a che fare con i fusi orari.
• Il tipo di dati timestamp è molto utile nella pratica, in quanto consente di memorizzare il timestamp completo - le date con l'ora esatta (ad esempio '2022-07-30 06:55:34'). La precisione può arrivare a 1 microsecondo. Come per il tipo di dati time, se si desidera includere informazioni sul fuso orario, è necessario utilizzare timestamp con fuso orario.
• Il tipo di dati interval è usato per memorizzare informazioni sugli intervalli di tempo (cioè sulla durata). È possibile restringere l'insieme dei campi memorizzati aggiungendo una frase corrispondente (ad esempio, YEAR, DAY, YEAR TO MONTH, HOUR TO SECOND); per maggiori dettagli, consultare la documentazione di PostgreSQL. Questi intervalli possono essere aggiunti o sottratti ai tipi di dati definiti sopra.

Per saperne di più su questi e altri tipi di dati PostgreSQL, leggete questo articolo; ora passiamo a un caso d'uso pratico.

Per creare la nostra tabella trips in PostgreSQL, utilizziamo il seguente codice:

CREATE TABLE trips(
    id int,
    destination varchar(32),
    departure_date date,
    return_date date,
    departure_timestamp timestamp with time zone,
    arrival_timestamp timestamp with time zone
);

INSERT INTO trips
VALUES (1, 'San Francisco', '2022-03-21', '2022-03-26', '2022-03-21 8:00 -04:00', '2022-03-21 11:14 -07:00');

INSERT INTO trips
VALUES (2, 'London', '2022-06-03', '2022-06-10', '2022-06-03 19:00 -04:00', '2022-06-04 07:10 +01:00');

INSERT INTO trips
VALUES (3, 'Sydney', '2022-07-28', '2022-08-10', '2022-07-28 15:00 -04:00', '2022-07-30 06:55 +10:00');

Si noti che utilizziamo il tipo di dati date per memorizzare le informazioni sulle date di partenza e di ritorno. Utilizziamo anche timestamp con fuso orario per memorizzare gli orari di partenza e di arrivo. Questo tipo di dati ci permette di memorizzare informazioni sulla data, l'ora e il fuso orario; dato che tutti i nostri viaggi attraversano diversi fusi orari, questo è importante.

Per esercitarci con le funzioni di PostgreSQL relative a data e ora, supponiamo di voler scoprire:

1. La durata del nostro viaggio in giorni.
2. La durata del nostro volo da casa a New York alla città di destinazione.
3. La data in cui dobbiamo iniziare a prepararci per il viaggio, che possiamo immaginare essere di circa 14 giorni.

Vogliamo queste informazioni per ogni viaggio nella nostra tabella. Ecco come trovarle usando PostgreSQL:

SELECT 
  destination, 
  return_date - departure_date + 1 AS trip_duration, 
  arrival_timestamp - departure_timestamp AS flight_duration,
  departure_date - INTERVAL '14 DAYS' AS prep_date
FROM trips;

Si noti che abbiamo aggiunto +1 per includere i giorni di partenza e di arrivo alla durata del viaggio. Ecco i risultati:

Ci sono molte altre cose che si possono fare con data e ora in PostgreSQL. Nella tabella seguente, ho riassunto come fare per:

• Ottenere la data e l'ora corrente.
• Sottrarre date/ore.
• Aggiungere/sottrarre intervalli.
• Estrarre alcune parti della data/ora.
• Creare una data/ora dalle sue parti.
• Troncare le date.
• Convertire stringhe in oggetti data/ora.
• Cambiare gli oggetti data/ora in stringhe con formattazione specifica.

Per maggiori dettagli su ciascuna delle funzioni sopra elencate, consultate la documentazione di PostgreSQL sui tipi di data/ora, sulle funzioni e gli operatori di data/ora e sulle funzioni di formattazione di data/ora.

Funzioni Oracle per la data e l'ora

Ecco i tipi di dati per l'elaborazione di date e orari nel database Oracle:

• DATE. Questo tipo di dati memorizza informazioni su data e ora, tra cui secolo, anno, mese, data, ora, minuto e secondo. Se non viene specificato un componente orario, l'ora predefinita è la mezzanotte.
• Il tipo di dati TIMESTAMP è un'estensione del tipo di dati DATE, in quanto memorizza anche i secondi frazionari.
  • TIMESTAMP WITH TIME ZONE è una variante del tipo di dati TIMESTAMP. Memorizza l'offset del fuso orario o il nome della regione del fuso orario.
  • TIMESTAMP WITH LOCAL TIME ZONE è un'altra variante di TIMESTAMP. Invece di memorizzare l'offset del fuso orario come parte dei dati della colonna, le informazioni sul fuso orario sono semplicemente normalizzate al fuso orario del database, cioè Oracle le restituisce nel fuso orario della sessione locale dell'utente.
• Il tipo di dati INTERVAL YEAR TO MONTH memorizza un periodo di tempo utilizzando i tipi di dati YEAR e MONTH
• Il tipo di dati INTERVAL DAY TO SECOND memorizza un periodo di tempo in termini di giorni, ore, minuti e secondi.

Per ulteriori informazioni sui tipi di dati datetime di Oracle, consultare la documentazione di Oracle; ora passiamo al nostro caso d'uso pratico. Creiamo ora la tabella trips nel database Oracle.

CREATE TABLE trips (
    id int,
    destination varchar(32),
    departure_date date,
    return_date date,
    departure_timestamp timestamp with time zone,
    arrival_timestamp timestamp with time zone
);
   
INSERT INTO trips
VALUES (1, 'San Francisco', DATE'2022-03-21', DATE'2022-03-26', TIMESTAMP'2022-03-21 8:00:00 -04:00', TIMESTAMP'2022-03-21 11:14:00 -07:00');

INSERT INTO trips
VALUES (2, 'London', DATE'2022-06-03', DATE'2022-06-10', TIMESTAMP'2022-06-03 19:00:00 -04:00', TIMESTAMP'2022-06-04 07:10:00 +01:00');

INSERT INTO trips
VALUES (3, 'Sydney', DATE'2022-07-28', DATE'2022-08-10', TIMESTAMP'2022-07-28 15:00:00 -04:00', TIMESTAMP'2022-07-30 06:55:00 +10:00');

Come si può vedere, stiamo usando il tipo di dati DATE per le date di partenza e di ritorno. (Non abbiamo bisogno di informazioni sul fuso orario o di precisione fino a frazioni di secondo). Utilizziamo TIME WITH TIME ZONE per gli orari di partenza e di arrivo, poiché abbiamo dati relativi a diversi fusi orari.

Quando si inseriscono i valori, si specifica il tipo di dati per ogni valore. In alternativa, si può utilizzare il formato predefinito di Oracle senza specificare il tipo di dati (ad esempio, '21-MAR-2022' e '21-MAR-2022 8.00.00 AM -04.00'). È possibile modificare i formati predefiniti utilizzando i parametri di inizializzazione NLS_DATE_FORMAT, NLS_DATE_LANGUAGE e NLS_TIMESTAMP_TZ_FORMAT. Per ulteriori informazioni, consultare la documentazione di Oracle.

Per ogni destinazione, vogliamo trovare la durata del viaggio in giorni (compresi i giorni di partenza e di arrivo), la durata del volo per la città di destinazione e la data in cui dobbiamo iniziare a prepararci per il viaggio, supponendo di voler iniziare con 14 giorni di anticipo.

Ecco come trovare queste informazioni in Oracle:

SELECT
  destination, 
  return_date - departure_date + 1 AS trip_duration, 
  arrival_timestamp - departure_timestamp AS flight_duration,
  departure_date - 14 AS prep_date
FROM trips;

In Oracle si possono fare molte altre cose con le date e gli orari. La tabella seguente riassume come:

• Ottenere la data e l'ora corrente.
• Sottrarre date e orari.
• Aggiungere/sottrarre intervalli.
• Estrarre alcune parti della data/ora.
• Troncare le date.
• Convertire stringhe in oggetti data/ora.
• Cambiare gli oggetti data/ora in stringhe con formattazione specifica.

Per maggiori dettagli su ciascuna delle funzioni sopra elencate e su altre utili funzioni di data e ora in Oracle, consultate questo articolo e la documentazione di Oracle.

Funzioni di data e ora in SQLite

SQLite non prevede tipi di dati particolari per la memorizzazione di date e orari. Tuttavia, le funzioni SQLite per i dati e l'ora possono aiutare a memorizzare date e orari come valori TEXT, REAL, o INTEGER:

• TEXT come stringhe ISO 8601 ('YYYY-MM-DD HH:MM:SS.SSS').
• REAL come numero di giorni dal mezzogiorno di Greenwich del 24 novembre 4714 a.C.
• INTEGER come numero di secondi dal 1970-01-01 00:00:00 UTC.

Ecco le funzioni data e ora di SQLite:

• La funzione date() restituisce la data nel formato AAAA-MM-GG.
• La funzione time() restituisce l'ora nel formato HH:MM:SS.
• La funzione datetime() restituisce il timestamp nel formato AAAA-MM-GG HH:MM:SS.
• La funzione julianday() restituisce il giorno giuliano, ossia il numero di giorni trascorsi dal mezzogiorno di Greenwich, in Inghilterra, il 24 novembre 4714 a.C.
• La funzione strftime() restituisce la data formattata secondo la stringa di formato specificata come primo argomento.

Per creare la nostra tabella dei viaggi in SQLite, possiamo utilizzare il seguente codice:

CREATE TABLE trips (
    id int,
    destination text,
    departure_date text,
    return_date text,
    departure_timestamp text,
    arrival_timestamp text
);

INSERT INTO trips
VALUES (1, 'San Francisco', '2022-03-21', '2022-03-26', '2022-03-21 08:00 -04:00', '2022-03-21 11:14 -07:00');

INSERT INTO trips
VALUES (2, 'London', '2022-06-03', '2022-06-10', '2022-06-03 19:00 -04:00', '2022-06-04 07:10 +01:00');

INSERT INTO trips
VALUES (3, 'Sydney', '2022-07-28', '2022-08-10', '2022-07-28 15:00 -04:00', '2022-07-30 06:55 +10:00');

Si noti che utilizziamo il tipo di dati TEXT per memorizzare tutte le date e gli orari. Quindi, inseriamo i valori specificando le date e gli orari nel formato abituale, includendo anche i fusi orari quando necessario.

Ora vogliamo esercitarci con le funzioni di data e ora di SQLite, calcolando la durata del nostro viaggio in giorni (compresi i giorni di partenza e di arrivo), la durata del volo in ore e la data in cui dobbiamo iniziare a prepararci per il viaggio, ovvero i soliti 14 giorni.

Ecco come calcolare questi dati in SQLite:

SELECT
  destination,
  julianday(return_date) - julianday(departure_date) + 1 AS trip_duration,
  (julianday(arrival_timestamp) - julianday(departure_timestamp)) * 24 AS flight_duration,
  date(departure_date, '-14 days') AS prep_date
FROM trips;

In questa query abbiamo utilizzato la funzione julianday() per calcolare la differenza tra due date/tempistiche. Questa funzione restituisce il numero di giorni, che è quello che ci aspettiamo per la durata del viaggio. La durata del volo è meglio presentata in ore - quindi, moltiplichiamo il risultato per 24 per ottenere la durata del volo in ore. Si noti anche l'eleganza con cui la funzione date() ci permette di aggiungere/sottrarre giorni da un valore di data. Analogamente, è possibile aggiungere/sottrarre anni, mesi, ore, minuti e secondi.

Vediamo cos'altro si può fare con date e orari in SQLite. Nella tabella seguente ho riassunto come fare:

• Ottenere la data e l'ora corrente.
• Sottrarre le date.
• Sottrarre orari.
• Aggiungere/sottrarre intervalli.
• Estrarre alcune parti della data/ora.
• Troncare le date.
• Convertire oggetti data/ora in stringhe con formattazione specifica.

A differenza delle tabelle che abbiamo per altri dialetti SQL, qui le colonne non corrispondono a tipi di dati specifici. Esse includono semplicemente esempi di elaborazione di (1) date, (2) orari e date e orari insieme (cioè timestamp).

Per maggiori dettagli sulle funzioni di data e ora di SQLite, consultare la documentazione di SQLite.

Funzioni di data e ora di MySQL

Esistono cinque tipi di dati per elaborare date e orari in MySQL:

• Il tipo di dati DATE è utilizzato per i valori con una parte di data ma senza una parte di tempo.
• Il tipo di dati DATETIME è utilizzato per i valori che contengono sia la parte relativa alla data che quella relativa all'ora. È anche possibile avere frazioni di secondo quando si inseriscono i valori di DATETIME nella tabella. The supported range is '1000-01-01 00:00:00' to '9999-12-31 23:59:59'.
• Il tipo di dati TIMESTAMP è anch'esso utilizzato per i valori che contengono sia la parte relativa alla data che quella relativa all'ora. Supporta anche i secondi frazionari. Inoltre, a partire da MySQL 8.0.19, questo tipo di dati supporta pienamente le informazioni sul fuso orario. L'intervallo supportato va da '1970-01-01 00:00:01' UTC a '2038-01-19 03:14:07' UTC.
• Il tipo di dati TIME è utilizzato per rappresentare l'ora del giorno e anche il tempo trascorso o l'intervallo di tempo tra due eventi. I valori di TIME possono andare da '-838:59:59' a '838:59:59'. Sono supportate le frazioni di secondo.
• Il tipo di dati YEAR viene utilizzato per rappresentare i valori dell'anno. L'intervallo supportato va da 1901 a 2155. Si noti inoltre che YEAR accetta valori in una varietà di formati, ad esempio '2021', 2021, '21', 21.

Per saperne di più sui tipi di dati e sui tipi di dati temporali in MySQL, consultare la documentazione di MySQL.

Ora ripetiamo l'esempio con i viaggi, ma questa volta in MySQL. Iniziamo creando la tabella trips tabella:

CREATE TABLE trips (
    id int,
    destination varchar(32),
    departure_date date,
    return_date date,
    departure_timestamp timestamp,
    arrival_timestamp timestamp
);

INSERT INTO trips
VALUES (1, 'San Francisco', '2022-03-21', '2022-03-26', '2022-03-21 08:00:00-04:00', '2022-03-21 11:14:00-07:00');

INSERT INTO trips
VALUES (2, 'London', '2022-06-03', '2022-06-10', '2022-06-03 19:00:00-04:00', '2022-06-04 07:10:00+01:00');

INSERT INTO trips
VALUES (3, 'Sydney', '2022-07-28', '2022-08-10', '2022-07-28 15:00:00-04:00', '2022-07-30 06:55:00+10:00');

Come si può vedere, utilizziamo il tipo di dati DATE per memorizzare le date di partenza e di ritorno. Per gli orari di partenza e di arrivo, abbiamo scelto il tipo di dati TIMESTAMP, poiché vogliamo conservare le informazioni sul fuso orario.

Per calcolare la durata del viaggio (compresi i giorni di partenza e di arrivo), la durata del volo e la data di inizio della preparazione (14 giorni prima), possiamo utilizzare la seguente query MySQL:

SELECT
    destination, 
    DATEDIFF(return_date, departure_date) + 1 AS trip_duration, 
    TIMEDIFF(arrival_timestamp, departure_timestamp) AS flight_duration,
    DATE_SUB(departure_timestamp, INTERVAL 14 DAY) AS prep_date
FROM trips;

Come si può notare da questa query, esistono alcune funzioni MySQL molto utili per elaborare date e orari. In realtà, esistono molte funzioni di questo tipo. Per avere un'idea di ciò che si può fare con le date e gli orari in MySQL, date un'occhiata alla tabella seguente e imparate come fare:

• Ottenere la data e l'ora corrente.
• Sottrarre date e orari.
• Aggiungere/sottrarre intervalli.
• Estrarre alcune parti della data/ora.
• Creare una data/ora dalle sue parti.
• Convertire stringhe in oggetti data/ora.
• Modificare la formattazione degli oggetti data/ora.

Per maggiori dettagli su ciascuna di queste funzioni, consultate la documentazione di MySQL sulle funzioni di data e ora.

Funzioni T-SQL per la data e l'ora

Infine, parliamo delle funzioni di data e ora che dovreste conoscere se lavorate in MS SQL Server e utilizzate Transact-SQL (T-SQL).

Innanzitutto, ecco i tipi di dati di data e ora supportati da T-SQL:

• Il tipo di dati time è utilizzato per memorizzare i valori di tempo, comprese le frazioni di secondo.
• Il tipo di dati date viene utilizzato per memorizzare valori di data senza parti di tempo.
• Il tipo di dati smalldatetime viene utilizzato in T-SQL per memorizzare date e orari nell'intervallo compreso tra 1900-01-01 e 2079-06-06, con una precisione massima di 1 minuto.
• Il tipo di dati datetime può memorizzare date e orari nell'intervallo da 1753-01-01 a 9999-12-31, con una precisione fino a 0,00333 secondi.
• T-SQL dispone anche del tipo di dati datetime2 che consente di memorizzare valori in un intervallo ancora più ampio. Memorizza valori in un intervallo ancora più ampio (da0001-01-01 00:00:00.0000000 a 9999-12-31 23:59:59.9999999) e definisce frazioni di secondo fino a 100 nanosecondi.
• Infine, il tipo di dati datetimeoffset memorizza date e orari con l'offset del fuso orario. Ha lo stesso intervallo e la stessa precisione del tipo di dati datetime2.

Per saperne di più sui tipi di dati e sui tipi di dati temporali in Transact-SQL , cliccate qui.

Ora è il momento di ripetere l'esempio dei viaggi con T-SQL. Come al solito, iniziamo creando la tabella trips tabella:

CREATE TABLE trips (
    id int,
    destination varchar(32),
    departure_date date,
    return_date date,
    departure_timestamp datetimeoffset,
    arrival_timestamp datetimeoffset
);

INSERT INTO trips
VALUES (1, 'San Francisco', '2022-03-21', '2022-03-26', '2022-03-21 08:00 -04:00', '2022-03-21 11:14 -07:00');

INSERT INTO trips
VALUES (2, 'London', '2022-06-03', '2022-06-10', '2022-06-03 19:00 -04:00', '2022-06-04 07:10 +01:00');

INSERT INTO trips
VALUES (3, 'Sydney', '2022-07-28', '2022-08-10', '2022-07-28 15:00 -04:00', '2022-07-30 06:55 +10:00');

Si noti che utilizziamo (1) il tipo di dati date per memorizzare le date di partenza e di ritorno e (2) il tipo di dati datetimeoffset per memorizzare gli orari di partenza e di arrivo (per conservare le informazioni sul fuso orario).

Ora calcoliamo la durata del viaggio (compresi i giorni di partenza e di arrivo), la durata del volo e la data di inizio della preparazione (14 giorni prima) utilizzando T-SQL:

SELECT
    destination, 
    DATEDIFF(day, departure_date, return_date) + 1 AS trip_duration, 
    DATEDIFF(hour, departure_timestamp, arrival_timestamp) AS flight_duration,
    DATEADD(day, -14, departure_timestamp) AS prep_date
FROM trips;

Come si può vedere da questo esempio, T-SQL dispone della funzione DATEDIFF() che elabora non solo le date, ma anche i valori di tempo e di data. Tuttavia, nel calcolare la durata del volo, siamo riusciti a ottenere solo il numero intero di ore invece dell'intervallo esatto con i minuti. Se avete bisogno di informazioni più precise, potete sempre selezionare un'altra parte di data per questa funzione (ad esempio, minuto per ottenere la durata del volo in minuti). Leggete questo articolo per vedere come elaborare l'output di questa funzione per ottenere l'intervallo nel formato richiesto.

La tabella seguente riassume alcune altre operazioni che è possibile eseguire con date e orari in T-SQL:

• Ottenere la data e l'ora corrente.
• Sottrarre date e orari.
• Aggiungere/sottrarre intervalli.
• Estrarre alcune parti della data/ora (ad esempio, mese, minuto).
• Creare data/ora dalle sue parti.
• Convertire stringhe in oggetti data/ora.
• Modificare i formati di data e ora.

Come si può vedere da questo esempio, T-SQL dispone della funzione DATEDIFF() che elabora non solo le date ma anche i valori di data e ora. Tuttavia, quando si calcola la durata del volo, è stato possibile ottenere solo il numero intero di ore invece dell'intervallo esatto con i minuti. Se avete bisogno di informazioni più precise, potete sempre selezionare un'altra parte di data per questa funzione (ad esempio, minuto per ottenere la durata del volo in minuti). Leggete questo articolo per vedere come elaborare l'output di questa funzione per ottenere l'intervallo nel formato richiesto.

La tabella seguente riassume alcune altre operazioni che è possibile eseguire con date e orari in T-SQL:

• Ottenere la data e l'ora corrente.
• Sottrarre date e orari.
• Aggiungere/sottrarre intervalli.
• Estrarre alcune parti della data/ora (ad esempio, mese, minuto).
• Creare data/ora dalle sue parti.
• Convertire stringhe in oggetti data/ora.
• Modificare i formati di data e ora.

Per maggiori dettagli sulle funzioni di data e ora di SQL Server, consultare la documentazione T-SQL.

Esercitiamoci con le funzioni di data e ora di SQL!

Spero che questo articolo vi abbia fornito una comprensione generale di come le date e gli orari possono essere elaborati in diversi dialetti SQL. Ora è il momento di fare pratica!

Vi consiglio di iniziare con un corso interattivo. In questo modo potrete acquisire una conoscenza completa delle funzioni SQL per i dati e l'ora, esercitarvi con esempi reali e ricevere una guida quando siete bloccati:

• Standard SQL Functions include 211 sfide di codifica. Si farà pratica con le funzioni SQL standard utilizzate per elaborare dati di testo, dati numerici, date e orari e altro ancora.
• Tipi di dati in SQL comprende 89 esercizi interattivi. Introduce ai tipi di dati più comuni in SQL Server, MySQL, Oracle e PostgreSQL.

Se volete ottenere una conoscenza completa di SQL partendo da zero esperienza di programmazione, vi raccomando la SQL from A to Z di apprendimento. Include 7 corsi interattivi che coprono la scrittura di query semplici, la combinazione di dati da più tabelle, la scrittura di query SQL complesse con subquery e l'uso di espressioni di tabella comuni, funzioni di finestra e altro ancora.

Grazie per la lettura e buon apprendimento.