Documentazione di PostgreSQL 9.0 > Il linguaggio SQL > Funzioni e Operatori > Funzioni di formattazione dei tipi di dato
PrecedenteConfronto con modelliFunzioni e operatori di data/orarioSuccessivo

9.8. Funzioni di formattazione dei tipi di dato

La funzioni di formattazione di PostgreSQL™ forniscono un potente insieme di strumenti per convertire diversi tipi di dati (date/orari, interi, a virgola mobile, numerici) ad una stringa formattata e per convertire da una stringa formattata agli specifici tipi di dato. La Tabella 9.20, «Funzioni di formattazione» le elenca. Tutte queste funzioni seguono una convenzione di chiamata comune: il primo argomento è il valore che sarà formattato e il secondo argomento è un modello che definisce il formato di uscita o di entrata.

La funzione to_timestamp può anche prendere un singolo argomento double precision per convertire da Unix epoch (i secondi da 01/01/1970 00:00:00+00) in timestamp with time zone. (L'Intero Unix epoch viene implicitamente convertito a doppia precisione).

Tabella 9.20. Funzioni di formattazione

FunzioneTipo restituitoDescrizioneEsempio
to_char(timestamp, text)textconverte un timestamp in stringato_char(current_timestamp, 'HH12:MI:SS')
to_char(interval, text)textconverte un intervallo in una stringato_char(interval '15h 2m 12s', 'HH24:MI:SS')
to_char(int, text)textconverte un intero in una stringato_char(125, '999')
to_char(double precision, text)textconverte un real/a doppia precisione in una stringato_char(125.8::real, '999D9')
to_char(numeric, text)textconverte un numeric in una stringato_char(-125.8, '999D99S')
to_date(text, text)dateconverte una stringa in una datato_date('05 Dec 2000', 'DD Mon YYYY')
to_number(text, text)numericconverte una stringa in un numericto_number('12,454.8-', '99G999D9S')
to_timestamp(text, text)timestamp with time zoneconverte stringhe in tipestampto_timestamp('05 Dec 2000', 'DD Mon YYYY')
to_timestamp(double precision)timestamp with time zoneconverte Unix epoch in timestampto_timestamp(1284352323)

In una stringa di modello di output per la funzione to_char, ci sono certi schemi che sono riconosciuti e sostituiti con dati appropriatamente formattati in base al valore fornito. Ogni testo che non è un modello viene semplicemente copiato letteralmente. Similmente, in una stringa di modello di input (per le altre funzioni), gli schemi di modelli identificano i valori da essere forniti dalla stringa di input dei dati.

La Tabella 9.21, «Modelli di maschere per formattazioni Data/Orario» mostra i modelli di maschere disponibili per formattare valori di data e orario.

Tabella 9.21. Modelli di maschere per formattazioni Data/Orario

ModelloDescrizione
HHora del giorno (01-12)
HH12ora del giorno (01-12)
HH24ora del giorno (00-23)
MIminuti (00-59)
SSsecondi (00-59)
MSmillisecondi (000-999)
USmicrosecondi (000000-999999)
SSSSsecondi dopo la mezzanotte (0-86399)
AM o A.M. or PM o P.M.indicatore mattina pomeriggio (maiuscolo)
am o a.m. or pm o p.m.indicatore mattina pomeriggio (minuscolo)
Y,YYYanno (4 e più cifre) con virgola
YYYYanno (4 e più cifre)
YYYultime 3 cifre dell'anno
YYultime 2 cifre dell'anno
Yultima cifra dell'anno
IYYYanno ISO (4 e più cifre)
IYYultime 3 cifre dell'anno ISO
IYultime 2 cifre dell'anno ISO
Iultima cifra dell'anno ISO
BC o B.C. or AD o A.D.indicatore dell'era (maiuscolo)
bc o b.c. or ad o a.d.indicatore dell'era (minuscolo)
MONTHnome del mese completo in maiuscolo (allungato a 9 caratteri con blank)
monthnome del mese completo in caratteri minuscoli (allungato a 9 caratteri con blank)
MONnome del mese abbreviato in maiuscolo (3 caratteri in inglese, in varie lunghezze se localizzato)
Monnome del mese abbreviato in caratteri misti (3 caratteri in inglese, in varie lunghezze se localizzato)
monnome del mese abbreviato in minuscolo (3 caratteri in inglese, in varie lunghezze se localizzato)
MMnumero del mese (01-12)
DAYnome del giorno completo in maiuscolo (allungato a 9 caratteri con blank)
Daynome del giorno completo in caratteri misti (allungato a 9 caratteri se localizzato)
daynome del giorno completo in minuscolo (allungato a 9 caratteri con blank)
DYnome del giorno abbreviato in maiuscolo (3 caratteri in inglese, in varie lunghezze se localizzato)
Dynome del giorno abbreviato in caratteri misti (3 caratteri in inglese, in varie lunghezzza se localizzato)
dynome del giorno abbreviato in minuscolo (3 caratteri in inglese, in varie lunghezze se localizzato)
DDDgiorno dell'anno (001-366)
IDDDgiorno dell'anno ISO (001-371; il giorno 1 dell'anno è il lunedi della prima settimana ISO).
DDgiorno del mese (01-31)
Dgiorno della settimana, Domenica(1) a Sabato(7)
IDgiorno della settimana ISO, Lunedi(1) a Domenica(7)
Wsettimana del mese (1-5) (La prima settimana comincia dal primo giorno del mese).
WWnumero di settimana dell'anno (1-53) (La prima settimana comincia dal primo giorno dell'anno).
IWnumero di settimana dell'anno ISO (1 - 53; il primo giovedi del nuovo anno è nella settimana 1).
CCsecolo (2 cifre) (Il ventunesimo secolo comincia il 2001-01-01)
JGiorno Giuliano (i giorni cominciano dalla mezzanotte del 24 Novembre 4714 AC)
Qtrimestre
RMmese in numeri romani (I-XII; I=Gennaio) (maiuscolo)
rmmese in numeri romani (i-xii; i=Gennaio) (minuscolo)
TZnome di fuso orario (maiuscolo)
tznome di fuso orario (minuscolo)

Certi modificatori possono essere applicati ad ogni modello di maschera per modificare il suo comportamento. Per esempio, FMMonth è la maschera Month con il modificatore FM. La Tabella 9.22, «Modificatori modelli di maschere per formattazioni Data/Orario» mostra i modificatori di modelli per le formattazioni data/orario.

Tabella 9.22. Modificatori modelli di maschere per formattazioni Data/Orario

ModificatoreDescrizioneEsempio
FM prefissomodo riempimento (sopprime i blank e gli zero di riempimento)FMMonth
suffisso THsuffiso per numero ordinale maiuscoloDDTH, ad es., 12TH
suffisso thsuffisso per numero ordinale minuscoloDDth, ad es., 12th
prefisso FXopzione globale di formato fissato (vedere le note di uso)FX Month DD Day
prefisso TMmodoi di traduzione (stampa i nomi del giorno e del mese basati su lc_time)TMMonth
suffisso SPmodo spell (non ancora implementato)DDSP

Note d'ustilizzo per la formattazione di data/orario:

  • FM sopprime gli zeri frontali e gli spazi vuoti in coda che altrimenti sarebbero aggiunti per far sì che l'output sia a larghezza fissa. In PostgreSQL™, FM modifica solo la prossima specifica, mentre in Oracle, FM ha effetto su tutti le specificazioni seguenti, e i modificatori ripetuti FM attiva e disattiva il modo fill.

  • TM non include spazi bianchi in coda.

  • to_timestamp e to_date saltano i diversi spazi bianchi nella stringa di input a meno che non venga usata l'opzione FX. Per esempio, to_timestamp('2000 JUN', 'YYYY MON') funziona, ma to_timestamp('2000 JUN', 'FXYYYY MON') restituisce un errore, perchè to_timestamp si aspetta un solo spazio. FX dev'essere specificato come primo elemento nel modello.

  • Il comune testo è ammesso nelle maschere to_char e verrà restituito in uscita letteralmente. È possibile immettere una sottostringa tra doppi apici per forzarne l'interpretazione come testo letterale anche se la stessa contiene parole chiave del modello. Per esempio, in '"Hello Year "YYYY', YYYY verrà rimpiazzato dal dato dell'anno, ma il singolo Y di Year rimarrà invariato. In to_date, to_number e to_timestamp, stringhe con doppi apici saltano il numero di caratteri di input contenuti nella stringa, ad es. "XX" salta due caratteri di input.

  • Se si vogliono avere doppi apici in output è necessario farli precedere da una barra inversa, per esempio E'\\"YYYY Month\\"'. (Sono necessarie due barre inverse perchè la barra inversa ha già uno speciale significato quando si utilizza la sintassi escape di stringa).

  • La conversione di YYYY da stringa a timestamp o date ha una restrizione se si utilizza un anno con più di 4 cifre. Bisognerà usare qualche carattere che non sia una cifra o una maschera dopo YYYY, altrimenti l'anno verrà sempre interpretato essere di 4 cifre. Per esempio (con l'anno 20000): to_date('200001131', 'YYYYMMDD') sarà interpretato come un anno di 4 cifre; usare invece un separatore che non sia una cifra dopo l'anno, tipo to_date('20000-1131', 'YYYY-MMDD') o to_date('20000Nov31', 'YYYYMonDD').

  • Nelle conversioni da stringa a timestamp o date, il campo CC è ignorato se c'è un campo YYY, YYYY o Y,YYY. Se CC è utilizzato con YY o Y allora l'anno viene calcolato come (CC-1)*100+YY.

  • Una settimana in ISO (invece di una data Gregoriana) può essere specificata per to_timestamp e to_date in due modi: to_date in one of two ways:

    • Anno, settimana e giorno della settimana: per esempio to_date('2006-42-4', 'IYYY-IW-ID') restituisce la data 2006-10-19. Se si omette il giorno della settimana viene supposto che sia 1 (Lunedi).

    • Anno e giorno dell'anno: per esempio to_date('2006-291', 'IYYY-IDDD') anch'esso restituisce 2006-10-19.

    Il tentativo di costruire una data usando un miscuglio di settimana ISO e campi data Gregoriani non ha senso, e causerà un errore. Nel contesto di anno ISO, il concetto di «mese» o «giorno del mese» non ha significato. Nel contesto di anno Gregoriano, la settimana ISO non ha significato. Gli utenti dovranno evitare di mischiare le specifiche delle date Gregoriane e ISO.

  • In una conversione da stringa a timestamp, i valori dei millisecondi (MS) e microsecondi (US) sono usati come parte del secondo dopo il punto decimale. Per esempio to_timestamp('12:3', 'SS:MS') non è 3 millisecondi, ma 300, perchè il conteggio nella conversione è 12 + 0.3 secondi. Questo significa, per il formato SS:MS, i valori di input 12:3, 12:30, e 12:300 specificano lo stesso numero di millisecondi. Per ottenere tre millisecondi, bisogna usare 12:003, dove la conversione è considerata come 12 + 0.003 = 12.003 secondi.

    Questo è un esempio più complesso: to_timestamp('15:12:02.020.001230', 'HH:MI:SS.MS.US') è 15 ore, 12 minuti, e 2 secondi + 20 millisecondi + 1230 microsecondi = 2.021230 secondi.

  • La numerazione del giorno della settimana di to_char(..., 'ID') uguaglia la funzione extract('isodow', ...), ma to_char(..., 'D') non uguaglia la numerazione dei giorni di extract('dow', ...).

  • La funzione to_char(interval) formatta HH e HH12 come in un orologio da 12 ore, per es. l'ora 0 e 36 diventa in output 12, mentre HH24 rende in output il valore intero dell'ora, che può eccedere di 23 per gli intervalli.

La tabella Tabella 9.23, «Modelli di maschere per formattazioni numeriche» mostra i modelli della maschere disponibili per formattare valori numerici.

Tabella 9.23. Modelli di maschere per formattazioni numeriche

ModelloDescrizione
9valore con il numero di cifre specificato
0valore con zeri non significativi in testa
. (punto)punto decimale
, (virgola)separatore di gruppo (migliaia)
PRvalori negativi racchiusi tra i simboli maggiore e minore
Ssegno ancorato al numero (usa la localizzazione)
Lsimbolo di valuta (usa la localizzazione)
Dpunto decimale (usa la localizzazione)
Gseparatore di gruppo (usa la localizzazione)
MIsegno meno in una specificata posizione (se il numero è < 0)
PLsegno più in una specificata posizione(if number > 0)
SGsegno più/meno in una specificata posizione
RNnumero romano (input tra 1 e 3999)
TH or thsuffisso per numero ordinale
Vslittamento di uno specificato numero di cifre (vedere le note)
EEEEnotazione scientifica (non ancora implementata)

Note di uso per la formattazione numerica:

  • Un segno formattato utilizzando SG, PL, o MI non è ancorato al numero; per esempio, to_char(-12, 'MI9999') produce '- 12' ma to_char(-12, 'S9999') produce ' -12'. L'implementazione Oracle non permette l'uso di MI prima di 9, ma nemmeno richiede che 9 preceda MI.

  • I 9 generano un valore con un numero di cifre identico alla quantità di 9 inseriti. Se una cifra non è disponibile, in output verrà prodotto uno spazio.

  • TH non converte valori inferiori a zero e non converte numeri frazionali.

  • PL, SG e TH sono estensioni di PostgreSQL™.

  • V moltiplica effettivamente i valori in input per 10^n, dove n è il numero di cifre che seguono V. La funzione to_char non supporta l'uso di V combinato con un punto decimale. (Es., 99.9V99 non è consentito).

  • L'EEEE (notazione scientifica) non può essere usata in combinazione con nessuno degli altri modelli di formattazione o modificatori diversi da modelli di cifra o punti decimali, e deve essere alla fine della stringa di formattazione (per es., 9.99EEEE è un modello valido).

Certi modificatori possono essere applicati a qualsiasi modello per alterare il loro comportamento. Per esempio, FM9999 è il modello 9999 con il modificatore FM. La Tabella 9.24, «Template Pattern Modifiers for Numeric Formatting» mostra i modificatori di modello per la formattazione dei numeri.

Tabella 9.24. Template Pattern Modifiers for Numeric Formatting

ModificatoreDescrizioneEsempio
prefisso FMmodalità fill (sopprime spazi bianchi e zeri in più)FM9999
suffisso THsuffisso maiuscolo per numeri ordinali999TH
suffisso thsuffisso minuscolo per numeri ordinali999th

La Tabella 9.25, «Esempi della funzione to_char» mostra alcuni esempi di utilizzo della funzione to_char.

Tabella 9.25. Esempi della funzione to_char

EspressioneRisultato
to_char(current_timestamp, 'Day, DD HH12:MI:SS')'Tuesday , 06 05:39:18'
to_char(current_timestamp, 'FMDay, FMDD HH12:MI:SS')'Tuesday, 6 05:39:18'
to_char(-0.1, '99.99')' -.10'
to_char(-0.1, 'FM9.99')'-.1'
to_char(0.1, '0.9')' 0.1'
to_char(12, '9990999.9')' 0012.0'
to_char(12, 'FM9990999.9')'0012.'
to_char(485, '999')' 485'
to_char(-485, '999')'-485'
to_char(485, '9 9 9')' 4 8 5'
to_char(1485, '9,999')' 1,485'
to_char(1485, '9G999')' 1 485'
to_char(148.5, '999.999')' 148.500'
to_char(148.5, 'FM999.999')'148.5'
to_char(148.5, 'FM999.990')'148.500'
to_char(148.5, '999D999')' 148,500'
to_char(3148.5, '9G999D999')' 3 148,500'
to_char(-485, '999S')'485-'
to_char(-485, '999MI')'485-'
to_char(485, '999MI')'485 '
to_char(485, 'FM999MI')'485'
to_char(485, 'PL999')'+485'
to_char(485, 'SG999')'+485'
to_char(-485, 'SG999')'-485'
to_char(-485, '9SG99')'4-85'
to_char(-485, '999PR')'<485>'
to_char(485, 'L999')'DM 485
to_char(485, 'RN')' CDLXXXV'
to_char(485, 'FMRN')'CDLXXXV'
to_char(5.2, 'FMRN')'V'
to_char(482, '999th')' 482nd'
to_char(485, '"Good number:"999')'Good number: 485'
to_char(485.8, '"Pre:"999" Post:" .999')'Pre: 485 Post: .800'
to_char(12, '99V999')' 12000'
to_char(12.4, '99V999')' 12400'
to_char(12.45, '99V9')' 125'
to_char(0.0004859, '9.99EEEE')' 4.86e-04'

Documentazione di PostgreSQL 9.0 > Il linguaggio SQL > Funzioni e Operatori > Funzioni di formattazione dei tipi di dato
PrecedenteConfronto con modelliFunzioni e operatori di data/orarioSuccessivo