Continuando la nostra serie di tipi di dati PostgreSQL oggi introdurremo i tipi di dati data, timestamp e intervallo.

L’implementazione PostgreSQL del calendario è molto buona e mostreremo alcuni esempi di topi su quanto sia confusa questa questione. Il time zonenotion in particolare è principalmente uno strumento politico in questi giorni, e rende nosense su un principio di ingegneria: non c’è modo di risolvere i problemi di time zoneproblems da principi di prima mano!,

Data/Ora e fusi orari

Gestire date e orari e fusi orari è una questione molto complessa, e su thistopic, puoi leggere il pezzo di Erik Naggum The Long, Painful History ofTime.

I capitoli della documentazione di PostgreSQL con i titoli Date/TimeTypes,Data Type FormattingFunctions e Date / Time Functions andOperatorscover tutto ciò che devi sapere su data,ora, timestamp e fusi orari withPostgreSQL.

La prima domanda a cui dobbiamo rispondere qui riguarda l’utilizzo di timestamp con orwithout fusi orari dalle nostre applicazioni., La risposta è semplice: usa sempre timestamp CON fusi orari.

Un mito comune è che la memorizzazione di fusi orari sarà certamente aggiungere al vostro storageand impronta di memoria. In realtà non è il caso di:

select pg_column_size(timestamp without time zone 'now'), pg_column_size(timestamp with time zone 'now');

 pg_column_size │ pg_column_size ════════════════╪════════════════ 8 │ 8(1 row)

PostgreSQL predefinito utilizzando bigint internamente per memorizzare i timestamp, e su disco e in memoria di formato sono uguali con o senza il tempo zonesupport., Ecco tutta la loro definizione di tipo a PostgreSQL codice sorgente(in src/include/datatype/timestamp.h):

typedef int64 Timestamp;typedef int64 TimestampTz;

la documentazione di PostgreSQL per gli orari, ecco come funziona:

Per timestamp con il fuso orario, la memoria interna è sempre UTC(Universal Coordinated Time), tradizionalmente conosciuto come il Greenwich Mean Time (GMT). Un valore di input con un fuso orario esplicito specificato viene convertito in UTC utilizzando l’offset appropriato per quel fuso orario., Se nessun fuso orario è classificato nella stringa di input, si presume che si trovi nella zona orariaindicata dal parametro Fuso orario del sistema e viene convertita in Utcusando l’offset per la zona del fuso orario.

PostgreSQL non memorizza il fuso orario da cui provengono con il timestamp.Invece converte da e verso il fuso orario di input e output molto simile a quello che abbiamo visto per il testo con client_encoding.

In questo script, giochiamo con l’impostazione del fuso orario del cliente e cambiamo da un valore francese a un altro valore francese, poiché Tahiti è un’isola nel Pacifico che fa parte della Francia., Ecco l’output completo come visto durante l’eseguimentoquesto script, quando viene lanciato con psql -a -f tz.sql:

In primo luogo, vediamo che la funzione now() restituisce sempre lo stesso timestampwithin una singola transazione. Se vuoi vedere l’orologio in esecuzione mentre in atransaction, usa invece la funzione clock_timestamp ().

Quindi, vediamo che quando cambiamo l’impostazione del client del fuso orario, PostgreSQLoutputs timestamp come previsto, nel fuso orario selezionato., Se gestisci un’applicazione con utenti in fusi orari diversi e vuoi visualizzare il timein il proprio fuso orario preferito locale, puoi impostare il fuso orario nel tuo codice di applicazione prima di eseguire qualsiasi elaborazione relativa al timestamp e havePostgreSQL fa tutto il duro lavoro per te.,

Infine, quando si seleziona indietro dalla tabella tstz, vediamo che la colonnatstz si rende conto che entrambi i valori inseriti in realtà sono lo stesso punto intime, ma visto da luoghi diversi nel mondo, mentre la colonna ts rende impossibile confrontare le voci e rendersi conto che in realtà sono accadute esattamente nello stesso momento.

Come detto prima, anche quando si utilizzano timestamp con fuso orario, PostgreSQL non memorizzerà il fuso orario in uso al momento dell’input, quindi non c’è modo dalla tabella ourtstz di sapere che le voci sono allo stesso tempo ma solo da luoghi diversi.,

L’apertura di questa sezione si collega alla lunga e dolorosa storia del tempo, e se non l’hai ancora letto, maybenow è un buon momento. Permettetemi di citare una parte rilevante dell’articolo qui:

Il problema di base con il tempo è che dobbiamo esprimere sia il tempo che il luogo ogni volta che vogliamo collocare qualche evento nel tempo e nello spazio, eppure tendiamo ad assumere coordinate spaziali ancor più di quanto assumiamo coordinate temporali, e nel caso del tempo nella comunicazione ordinaria, è semplicemente lasciato fuori del tutto., Nonostante l’esistenza di fusi orari e regimi di tempo di risparmio strangedaylight in tutto il mondo, la maggior parte delle persone sono blithelyunaware del proprio fuso orario e certamente di come si riferisce a standardreferences. La maggior parte delle persone è ugualmente inconsapevole che scegliendo una notazioneche è vicina all’espressione parlata o scritta delle date, lo rendono privo di significato per le persone che potrebbero non condividere la cultura, ma possono ancora leggere la lingua., È improbabile che la gente cambierà abbastanza per mettere theseissues per riposare, così la gente di computer responsabile ha bisogno di affrontare il issuesand resiste all’impulso altrimenti opprimente di abbreviare e dropcontext.

Sono disponibili diverse opzioni per inserire i valori di timestamp in PostgreSQL. Theeasiest è quello di utilizzare il formato ISO, quindi se il codice dell’applicazione consente thatyou’re tutti insieme. Nell’esempio seguente lasciamo fuori il fuso orario, comedi solito, viene gestito dal parametro della sessione del fuso orario, come visto sopra., Ifyou necessario, naturalmente, è possibile inserire il fuso orario in timestamp valuesdirectly:

select timestamptz '2017-01-08 04:05:06', timestamptz '2017-01-08 04:05:06+02';

Al di inserimento o di aggiornamento, utilizzare la stessa stringhe letterali senza typedecoration: PostgreSQL conosce già il tipo di colonna di destinazione, e ituses che per analizzare i valori letterali nell’istruzione DML.

Alcuni casi d’uso dell’applicazione richiedono solo la data. Quindi utilizzare il tipo di dati datain PostgreSQL., Ovviamente è possibile confrontare una data e atimestamp con il fuso orario nelle query SQL e persino aggiungere un timeoffset in cima alla data per costruire un timestamp.

Intervalli di tempo

PostgreSQL implementa un tipo di dati interval insieme ai tipi di dati time, dateand timestamptz.,razione, come amonth o due settimane, o anche un millisecondo:

set intervalstyle to postgres;select interval '1 month', interval '2 weeks', 2 * interval '1 week', 78389 * interval '1 ms';

Il default PostgreSQL output simile a questo:

 interval │ interval │ ?column? │ ?column? ══════════╪══════════╪══════════╪══════════════ 1 mon │ 14 days │ 14 days │ 00:01:18.389(1 row)

Diverse intervalstyle valori sono possibili, e il settingpostgres_verbose è abbastanza bello per interattivi psql sessioni:

set intervalstyle to postgres_verbose;select interval '1 month', interval '2 weeks', 2 * interval '1 week', 78389 * interval '1 ms';

Questa volta abbiamo un user-friendly di uscita:

 interval │ interval │ ?column? │ ?column? ══════════╪═══════════╪═══════════╪═════════════════════ @ 1 mon │ @ 14 days │ @ 14 days │ @ 1 min 18.389 secs(1 row)

Quanto è lungo un mese?, Bene, dipende da quale mese, e PostgreSQL sa che:

Quando si collega un intervallo a una data o timestamp in PostgreSQL, il numero di giorni in quell’intervallo si adatta alla specifica entrata del calendario che hai scelto. Altrimenti, un intervallo di un mese è considerato 30giorni. Qui vediamo che calcolare l’ultimo giorno di febbraio è molto semplice:

Conclusione

L’implementazione del calendario di PostgreSQL è molto buona, quindi usala!,

Questo articolo è un estratto dal mio libro Mastering PostgreSQL in ApplicationDevelopment, che insegna SQL todevelopers in modo che possono sostituire le migliaia di righe di codice con verysimple query. Il libro ha un capitolo completo sui tipi di dati in PostgreSQL, dai un’occhiata!