Fusi orari in SQL DATE vs java.sql.Date

Mi sento un po ‘confuso dal comportamento del tipo di dati SQL DATE rispetto a quello di java.sql.Date . Prendi la seguente dichiarazione, ad esempio:

 select cast(? as date) -- in most databases select cast(? as date) from dual -- in Oracle 

Prepariamoci ed eseguiamo la dichiarazione con Java

 PreparedStatement stmt = connection.prepareStatement(sql); stmt.setDate(1, new java.sql.Date(0)); // GMT 1970-01-01 00:00:00 ResultSet rs = stmt.executeQuery(); rs.next(); // I live in Zurich, which is CET, not GMT. So the following prints -3600000, // which is CET 1970-01-01 00:00:00 // ... or GMT 1969-12-31 23:00:00 System.out.println(rs.getDate(1).getTime()); 

In altre parole, il timestamp GMT che leggo alla dichiarazione diventa il timestamp CET che torno. In quale fase è aggiunto il fuso orario e perché?

Nota:

La specifica JDBC non definisce alcun dettaglio per quanto riguarda il fuso orario. Nondimeno, molti di noi conoscono il dolore di dover affrontare le discrepanze del fuso orario JDBC; guarda tutte le domande di StackOverflow !

In definitiva, la gestione del fuso orario per i tipi di database di data / ora si riduce al server di database, al driver JDBC ea tutto ciò che si trova in mezzo. Sei persino in balia dei bug dei driver JDBC; PostgreSQL ha corretto un bug nella versione 8.3 dove

I metodi Statement.getTime, .getDate e .getTimestamp che sono passati a un object Calendar ruotavano il fuso orario nella direzione sbagliata.

Quando crei una nuova data utilizzando la new Date(0) (asserisci che stai usando Oracle JavaSE java.sql.Date , la tua data viene creata

utilizzando il valore temporale dei millisecondi specificato. Se il valore dei millisecondi specificato contiene informazioni sull’ora, il driver imposterà i componenti dell’ora sull’ora nel fuso orario predefinito (il fuso orario della macchina virtuale Java che esegue l’applicazione) che corrisponde a zero GMT.

Quindi, la new Date(0) dovrebbe utilizzare GMT.

Quando si chiama ResultSet.getDate(int) , si sta eseguendo un’implementazione JDBC. Le specifiche JDBC non determinano il modo in cui un’implementazione JDBC deve gestire i dettagli del fuso orario; quindi sei in balia dell’implementazione. Guardando Oracle 11g oracle.sql.DATE JavaDoc, non sembra che Oracle DB memorizzi le informazioni sul fuso orario, quindi esegue le proprie conversioni per ottenere la data in un java.sql.Date . Non ho esperienza con Oracle DB, ma suppongo che l’implementazione JDBC stia utilizzando le impostazioni del fuso orario del server e della propria JVM locale per eseguire la conversione da oracle.sql.DATE a java.sql.Date .


Si dice che più implementazioni RDBMS gestiscono correttamente il fuso orario, ad eccezione di SQLite. Diamo un’occhiata a come funzionano H2 e SQLite quando si inviano valori di data al driver JDBC e quando si ottengono valori di data dal driver JDBC.

Il driver H2 JDBC PrepStmt.setDate(int, Date) utilizza ValueDate.get(Date) , che chiama DateTimeUtils.dateValueFromDate(long) che esegue una conversione del fuso orario.

Utilizzando questo driver JDBC SQLite , PrepStmt.setDate(int, Date) chiama PrepStmt.setObject(int, Object) e non esegue alcuna conversione del fuso orario.

Il driver JDBC H2 JdbcResultSet.getDate(int) restituisce get(columnIndex).getDate() . get(int) restituisce un Value H2 per la colonna specificata. Poiché il tipo di colonna è DATE , H2 utilizza ValueDate . ValueDate.getDate() chiama DateTimeUtils.convertDateValueToDate(long) , che alla fine crea un java.sql.Date dopo una conversione del fuso orario.

Utilizzando questo driver JDBC SQLite , il RS.getDate(int) è molto più semplice; restituisce semplicemente un java.sql.Date utilizzando il valore di data estesa memorizzato nel database.

Quindi vediamo che il driver H2 JDBC è intelligente nel gestire le conversioni del fuso orario con le date mentre il driver JDBC di SQLite non lo è (per non dire che questa decisione non è intelligente, potrebbe adattarsi bene alle decisioni di progettazione di SQLite). Se si insegue il codice sorgente per gli altri driver JDBC RDBMS che hai citato, probabilmente troverai che la maggior parte si avvicina alla data e al fuso orario in modo simile a come fa H2.

Anche se le specifiche JDBC non dettagliano la gestione del fuso orario, è logico che i progettisti di implementazione RDBMS e JDBC prendessero in considerazione il fuso orario e lo gestissero correttamente; soprattutto se vogliono che i loro prodotti siano commercializzabili nell’arena globale. Questi designer sono dannatamente intelligenti e non sono sorpreso che molti di loro abbiano ragione, anche in assenza di specifiche concrete.


Ho trovato questo blog di Microsoft SQL Server, utilizzando i dati del fuso orario in SQL Server 2008 , che spiega come il fuso orario complica le cose:

i fusi orari sono un’area complessa e ogni applicazione dovrà affrontare il modo in cui si gestiranno i dati del fuso orario per rendere i programmi più facili da usare.

Sfortunatamente, non esiste un’autorità standard internazionale per i nomi e i valori del fuso orario. Ogni sistema deve utilizzare un sistema di propria scelta, e finché non esiste uno standard internazionale, non è ansible provare a fare in modo che SQL Server ne fornisca uno e alla fine causerà più problemi di quanti ne risolverebbe.

È il driver jdbc che esegue la conversione. Ha bisogno di convertire l’object Date in un formato accettabile dal formato db / wire e quando tale formato non include un fuso orario, la tendenza è quella di default all’impostazione del fuso orario della macchina locale quando si interpreta la data. Quindi, lo scenario più probabile, dato l’elenco dei driver che specifichi, è che tu imposti la data su GMT 1970-1-1 00:00:00 ma la data interpretata quando la impostavi sull’istruzione era CET 1970-1-1 01:00:00. Poiché la data è solo la parte della data, ottieni 1970-1-1 (senza fuso orario) inviata al server e ti viene restituita. Quando l’autista recupera la data e la accede come data, vede 1970-1-1 e la interpreta nuovamente con il fuso orario locale, ovvero CET 1970-1-1 00:00:00 o GMT 1969-12 -31 23:00:00. Quindi, hai “perso” un’ora rispetto alla data originale.

Sia java.util.Date che gli oggetti Oracle / MySQL Date sono semplicemente rappresentazioni di un punto nel tempo indipendentemente dalla posizione. Ciò significa che è molto probabile che venga memorizzato internamente come il numero di milli / nano secondi dall’epoca, GMT 1970-01-01 00:00:00.

Quando si legge dal set di risultati, la chiamata a “rs.getDate ()” indica al gruppo di risultati di acquisire i dati interni contenenti il ​​punto nel tempo e convertirli in un object Date java. Questo object data viene creato sul computer locale, quindi Java sceglierà il fuso orario locale, che è CET per Zurigo.

La differenza che stai vedendo è una differenza nella rappresentazione, non una differenza nel tempo.

La class java.sql.Date corrisponde a SQL DATE, che non memorizza le informazioni sul fuso orario o sul fuso orario. Il modo in cui questo viene realizzato è “normalizzando” la data, come la mette javadoc :

Per conformarsi alla definizione di SQL DATE, i valori millisecondi racchiusi da un’istanza java.sql.Date devono essere ‘normalizzati’ impostando le ore, i minuti, i secondi e i millisecondi a zero nel particolare fuso orario a cui l’istanza è associata .

Ciò significa che quando lavori in UTC + 1 e chiedi al database un DATE, un’implementazione conforms fa esattamente ciò che hai osservato: restituisci un java.sql.Date con un valore in millisecondi corrispondente alla data in questione alle 00:00 : 00 UTC + 1 indipendentemente da come i dati sono arrivati ​​al database in primo luogo.

I driver di database possono consentire di cambiare questo comportamento attraverso le opzioni se non è quello che vuoi.

D’altra parte, quando si passa un java.sql.Date al database, il driver utilizzerà il fuso orario predefinito per separare i componenti di data e ora dal valore millisecondo. Se si utilizza 0 e si è in UTC + X, la data sarà 1970-01-01 per X> = 0 e 1969-12-31 per X <0.


Sidenote: È strano vedere che la documentazione per il costruttore Date(long) differisce dall’implementazione. Il javadoc dice questo:

Se il valore dei millisecondi specificato contiene informazioni sull’ora, il driver imposterà i componenti dell’ora sull’ora nel fuso orario predefinito (il fuso orario della macchina virtuale Java che esegue l’applicazione) che corrisponde a zero GMT.

Tuttavia, ciò che è effettivamente implementato in OpenJDK è questo:

 public Date(long date) { // If the millisecond date value contains time info, mask it out. super(date); } 

Apparentemente questo “mascheramento” non è implementato. Proprio perché il comportamento specificato non è ben specificato, ad esempio dovrebbe 1970-01-01 00:00:00 GMT-6 = 1970-01-01 06:00:00 GMT essere mappato a 1970-01-01 00:00 : 00 GMT = 1969-12-31 18:00:00 GMT-6 o ​​1970-01-01 18:00:00 GMT-6?

C’è un sovraccarico di getDate che accetta un Calendar , potresti usare questo per risolvere il tuo problema? O potresti usare un object Calendar per convertire le informazioni.

Cioè, assumendo che il recupero sia il problema.

 Calendar gmt = Calendar.getInstance(TimeZone.getTimeZone("GMT")); PreparedStatement stmt = connection.prepareStatement(sql); stmt.setDate(1, new Date(0)); // I assume this is always GMT ResultSet rs = stmt.executeQuery(); rs.next(); //This will output 0 as expected System.out.println(rs.getDate(1, gmt).getTime()); 

In alternativa, supponendo che l’archiviazione sia il problema.

 Calendar gmt = Calendar.getInstance(TimeZone.getTimeZone("GMT")); PreparedStatement stmt = connection.prepareStatement(sql); gmt.setTimeInMillis(0) ; stmt.setDate(1, gmt.getTime()); //getTime returns a Date object ResultSet rs = stmt.executeQuery(); rs.next(); //This will output 0 as expected System.out.println(rs.getDate(1).getTime()); 

Non ho un ambiente di prova per questo, quindi dovrai scoprire qual è il presupposto corretto. Inoltre, ritengo che getTime restituisca le getTime locali correnti, altrimenti dovrai cercare una rapida conversione del fuso orario.