Funzioni di data PostgreSQL

La comprensione delle funzioni di data e ora nel vostro database è essenziale per un'analisi e un reporting efficaci dei dati. Continuate a leggere per saperne di più sulle funzioni di data di PostgreSQL.

Questo articolo illustra alcune delle più utili funzioni di data e ora di PostgreSQL e le loro applicazioni nell'analisi dei dati e nella creazione di report. Le funzioni di data SQL facilitano diverse attività di analisi dei dati, tra cui l'analisi delle vendite, il reporting finanziario, l'analisi dei siti web e altro ancora.

Questo articolo presenta gli strumenti, sotto forma di funzioni di data e ora PostgreSQL, utilizzati per svolgere questi compiti. Inizieremo con una rapida panoramica dei tipi di dati di data e ora disponibili in PostgreSQL prima di passare alle funzioni.

Consultate il nostro Ricettario SQL, che contiene una serie di ricette SQL per PostgreSQL e altri dialetti. Si tratta di una risorsa preziosa che vale la pena di tenere tra i preferiti per una rapida consultazione durante la scrittura delle query. Per saperne di più sul concetto di ricettario SQL, cliccate qui.

Tipi di dati di data e ora in PostgreSQL

In PostgreSQL sono disponibili diversi tipi di dati per memorizzare i valori di data e ora. Di seguito è riportata una panoramica dei tipi più frequentemente utilizzati:

Per l'analisi dei dati che devono prendere in considerazione diversi fusi orari, PostgreSQL offre tipi di dati come TIME WITH TIME ZONE (che memorizza i valori dell'ora con le informazioni sul fuso orario) e TIMESTAMP WITH TIME ZONE (che memorizza i valori di data e ora con le informazioni sul fuso orario).

Leggete il nostro articolo Una panoramica dei tipi di dati PostgreSQL per saperne di più sui tipi di dati disponibili in PostgreSQL. E se volete fare più pratica, date un'occhiata a questi 19 esercizi su PostgreSQL con soluzioni dettagliate.

Utilizzeremo tutti i tipi di dati di cui sopra per esplorare le funzioni di data e ora di PostgreSQL. Iniziamo!

Panoramica delle funzioni di data e ora in PostgreSQL

Tutte le funzioni di data e ora di PostgreSQL possono essere raggruppate nelle seguenti categorie:

• Confronto dei valori di data e ora
• Ottenere i valori correnti di data e ora
• Esecuzione di operazioni aritmetiche sui valori di data e ora
• Manipolazione dei valori di data e ora
• Formattazione dei valori di data e ora

Nelle sezioni seguenti sono elencate tutte le funzioni pertinenti con esempi.

Questa è la sales che utilizzeremo per i nostri esempi:

Confronto dei valori di data e ora

Per confrontare i valori di data e ora in PostgreSQL, si utilizzano gli operatori di confronto standard. Questi operatori includono =, !=, <>, >, >=, <, <=, BETWEEN, e NOT BETWEEN.

Di seguito sono riportati alcuni esempi di utilizzo di ciascun operatore di confronto.

Operatore di uguaglianza (=)

Domanda: Quante vendite si sono verificate il 1° giugno 2024?

Risposta:

SELECT COUNT(*) AS count
FROM sales
WHERE sale_date = '2024-06-01';

Operatore non uguale (!= o <>)

Domanda: Quante vendite non si sono verificate alle 10:00:00?

Risposta:

SELECT COUNT(*) AS count
FROM sales
WHERE sale_time != '10:00:00';

Operatore Maggiore di (>)

Domanda: Quante vendite si sono verificate dopo il 1° giugno 2024, alle ore 10:00:00?

Risposta:

SELECT COUNT(*) AS count
FROM sales
WHERE sale_timestamp > '2024-06-01 10:00:00';

Nota: quando si confrontano le date in SQL, l'operatore > significa che se la data X è maggiore della data Y, la data X è più lontana nel futuro (successiva) rispetto alla data Y.

Operatore Maggiore o uguale (>=)

Domanda: Quante vendite si sono verificate alle 10:00:00 del 1° giugno 2024?

Risposta:

SELECT COUNT(*) AS count
FROM sales
WHERE sale_timestamp >= '2024-06-01 10:00:00';

Nota: Quando si confrontano i timestamp in SQL, l'operatore >= significa che se il timestamp X è maggiore o uguale al timestamp Y, il timestamp X è più lontano nel futuro (successivo) rispetto al timestamp Y.

Operatore Meno di (<)

Domanda: Quante vendite si sono verificate prima delle 12:00:00 del 1° giugno 2024?

Risposta:

SELECT COUNT(*) AS count
FROM sales
WHERE sale_timestamp < '2024-06-01 12:00:00';

Nota: quando si confrontano i timestamp in SQL, l'operatore < significa che se il timestamp X è inferiore al timestamp Y, X è antecedente a Y.

Operatore Meno di o uguale (<=)

Domanda: Quante vendite si sono verificate il 1° giugno 2024 alle 12:00:00 o prima?

Risposta:

SELECT COUNT(*) AS count
FROM sales
WHERE sale_timestamp <= '2024-06-01 12:00:00';

Nota: Quando si confrontano date o orari in SQL, l'operatore <= significa che se la data X è inferiore o uguale alla data Y, la data X è precedente o uguale alla data Y.

Operatore BETWEEN

Domanda: Quante vendite si sono verificate tra le 10:00:00 e le 14:00:00?

Risposta:

SELECT COUNT(*) AS count
FROM sales
WHERE sale_timestamp BETWEEN '2024-06-01 10:00:00'
AND '2024-06-01 14:00:00';

Operatore NOT BETWEEN

Domanda: Quante vendite non si sono verificate tra le 10:00:00 e le 14:00:00?

Risposta:

SELECT COUNT(*) AS count
FROM sales
WHERE sale_timestamp NOT BETWEEN '2024-06-01 10:00:00'
AND '2024-06-01 14:00:00';

Buono a sapersi: È possibile ordinare l'output in base alla data, all'ora o al timestamp, semplicemente includendo la colonna corrispondente nella clausola ORDER BY.

Per saperne di più, consultare i seguenti ricettari:

• Come confrontare 2 date nella clausola WHERE in SQL
• Come confrontare i valori di data e ora in SQL
• Come confrontare i valori di una data in SQL
• Come ordinare per data in PostgreSQL o Oracle

Ottenere i valori attuali di data e ora

Quando si analizzano i dati, spesso si desidera confrontarli con il timestamp corrente ogni volta che la query viene eseguita. In PostgreSQL, questo può essere ottenuto con la funzione NOW() e con le parole chiave CURRENT_DATE, CURRENT_TIME e CURRENT_TIMESTAMP.

Importante: la funzione NOW() è specifica di PostgreSQL, mentre le funzioni CURRENT_* sono presenti in SQL standard e se ne consiglia l'uso al posto della funzione NOW().

ORA()

Utilizzare questa funzione per selezionare la data e l'ora correnti insieme al fuso orario.

SELECT NOW();

DATA CORRENTE

Utilizzare questa parola chiave per selezionare la data corrente.

SELECT CURRENT_DATE;

ORA_CORRENTE

Utilizzare questa parola chiave per selezionare l'ora corrente insieme al fuso orario (ma senza la data).

SELECT CURRENT_TIME;

TIMESTAMP_CORRENTE

Utilizzare questa funzione per selezionare la data e l'ora correnti insieme al fuso orario.

SELECT CURRENT_TIMESTAMP;

Per saperne di più, consultate questi ricettari:

• Come ottenere la data corrente in PostgreSQL
• Come ottenere l'ora corrente in PostgreSQL
• Come ottenere la data e l'ora correnti (senza fuso orario) in PostgreSQL
• Come ottenere la data e l'ora correnti con l'offset del fuso orario in PostgreSQL
• Come ottenere l'ora corrente (senza fuso orario) in PostgreSQL

Operazioni aritmetiche sui valori di data e ora

Le operazioni aritmetiche sui valori di data e ora in PostgreSQL includono il calcolo della differenza tra due date e l'aggiunta o la sottrazione di INTERVALs a o dai valori di data e ora.

Seguite gli esempi di utilizzo riportati di seguito per:

Calcolare la differenza tra due valori di data utilizzando la funzione AGE().

SELECT AGE(sale_date, CURRENT_DATE) AS age_difference
FROM sales
WHERE product_name = 'Apples';

Si noti che questa funzione restituisce l'INTERVALLO tra due valori.

Calcolare la differenza tra due valori di data utilizzando l'operatore meno (-).

SELECT sale_timestamp - CURRENT_TIMESTAMP AS difference
FROM sales
WHERE product_name = 'Apples';

Si noti che restituisce un intervallo tra due valori, come farebbe la funzione age().

Calcolare la differenza tra due valori temporali utilizzando l'operatore meno (-).

SELECT sale_time, sale_time - '09:00:00' AS difference
FROM sales
WHERE product_name = 'Apples';

Aggiungere gli INTERVALLI.

Utilizzare l'operatore più (+) per aggiungere un INTERVAL a un valore di data/ora:

SELECT sale_date + INTERVAL '1 day' AS next_day
FROM sales
WHERE product_name = 'Apples';

Sottrarre gli INTERVALLI.

Usare l'operatore meno (-) per sottrarre un INTERVAL da un valore di data/ora.

SELECT sale_date - INTERVAL '1 week' AS previous_week
FROM sales
WHERE product_name = 'Apples';

Per saperne di più, consultate questi ricettari:

• Come calcolare la differenza di data in PostgreSQL/Oracle
• Come calcolare la differenza di data e ora in PostgreSQL
• Come trovare l'intervallo tra due date in PostgreSQL
• Come ottenere la data di ieri in PostgreSQL

Manipolazione dei valori di data e ora

PostgreSQL fornisce funzioni che facilitano l'estrazione di parti specifiche di valori di data/ora, come DATE_PART(), DATE_TRUNC() e EXTRACT(). Nell'analisi dei dati, queste funzioni sono comunemente utilizzate per raggruppare i dati per giorno, mese o anno, consentendo così la creazione di report con statistiche su periodi di tempo definiti.

DATE_PART()

Estrae un componente specifico (ad esempio, anno, mese o giorno) da un valore di data/ora.

SELECT DATE_PART('year', sale_date) AS sale_year
FROM sales
WHERE product_name = 'Apples';

ESTRATTO()

Estrae una parte specifica (ad esempio, anno, mese o giorno) da un valore di data/ora.

SELECT EXTRACT(YEAR FROM sale_date) AS sale_year
FROM sales
WHERE product_name = 'Apples';

La funzione EXTRACT() può essere utilizzata in modo intercambiabile con la funzione DATE_PART(). Tuttavia, EXTRACT() può gestire i fusi orari.

DATE_TRUNC()

Tronca un valore di data/ora all'unità specificata (ad esempio, anno, mese o giorno).

SELECT DATE_TRUNC('year', sale_date) AS year_start
FROM sales
WHERE product_name = 'Apples';

FARE_DATA()

Assembla una data completa a partire da anno, mese e giorno.

SELECT MAKE_DATE('2024', '6', '1') AS date;

GENERA_SERIE()

Genera una serie di valori di data/ora con intervalli uguali.

SELECT GENERATE_SERIES(
	   '2024-06-01 08:00:00'::timestamp, 
	   '2024-06-01 09:00:00'::timestamp, 
	   '20 minutes'::interval
             ) AS generated_timestamp;

Per saperne di più, consultate questi ricettari:

• Come raggruppare per mese in PostgreSQL
• Come raggruppare per anno in SQL
• Come estrarre il numero della settimana da una data in PostgreSQL
• Come ottenere i nomi dei giorni in PostgreSQL
• Come ottenere il giorno dell'anno da una data in PostgreSQL
• Come ordinare per nome del mese in PostgreSQL o Oracle
• Come ottenere il mese precedente in SQL

Formattazione dei valori di data e ora

PostgreSQL offre diverse funzioni di formattazione che facilitano la lettura dei report. Queste funzioni includono TO_CHAR(), TO_DATE() e TO_TIMESTAMP().

TO_CHAR()

Converte un valore di data/ora in una stringa formattata secondo un formato specificato.

SELECT TO_CHAR(sale_date, 'MM/DD/YYYY') AS formatted_date
FROM sales
WHERE product_name = 'Apples';

SELECT TO_CHAR(sale_date, 'MM/DD/YYYY') AS formatted_date
FROM sales
WHERE product_name = 'Apples';

Per conoscere i formati di data/ora disponibili forniti da PostgreSQL, vedere qui.

TO_DATE()

Converte una stringa in un valore di data secondo un formato specificato.

SELECT TO_DATE('2024-06-01', 'YYYY-MM-DD') AS date_value;

TO_TIMESTAMP()

Converte una stringa in un valore di timestamp secondo un formato specificato.

SELECT TO_TIMESTAMP('2024-06-01 08:00:00', 'YYYY-MM-DD HH24:MI:SS')
AS timestamp_value;

Per saperne di più, consultate questi libri di cucina:

• Come formattare una data in PostgreSQL
• Come convertire una stringa in una data in PostgreSQL
• Come convertire una stringa in un timestamp in PostgreSQL

Lavorare con le funzioni di data e ora di PostgreSQL

Le funzioni di data e ora offerte da PostgreSQL sono strumenti fondamentali per l'analisi dei dati e la creazione di report. Possono aiutarci a capire i modelli di dati nel tempo, come le tendenze di vendita o i picchi di traffico di un sito web. Con queste funzioni possiamo raggruppare i dati per giorno, mese o anno, rendendo più facile individuare le tendenze e prendere decisioni informate. Inoltre, ci aiutano a formattare i nostri report, rendendoli più facili da leggere e da capire.

Poiché i dati sono spesso archiviati in database o file diversi, è possibile importare i dati in formato CSV in PostgreSQL per un'ulteriore elaborazione.

Se avete bisogno di un pratico promemoria sulle funzioni SQL, consultate il nostro foglio informativo gratuito Funzioni SQL standard. Potete scaricarlo, stamparlo e tenerlo a portata di mano mentre lavorate e imparate.

Se volete consolidare la vostra conoscenza di PostgreSQL, date un'occhiata al nostro corso completo SQL dalla A alla Z in PostgreSQL. Offre centinaia di esercizi per insegnarvi tutto ciò che dovete sapere per diventare un professionista di Postgres, compresi concetti avanzati come le funzioni finestra, le query ricorsive e PostGIS. Buon apprendimento!