Cristian Cudizio on the WEB

Oracle prevede tre tipi dato "timestamp":

• TIMESTAMP
• TIMESTAMP WITH TIME ZONE
• TIMESTAMP WITH LOCAL TIME ZONE

La mia esperienza mi dice che il tipo TIMESTAMP risparmia due byte perchè ha un'informazione in meno, la time zone ovvero la differenza di orario rispetto a greenich, però può creare grossi problemi in caso di differenza di fuso orario tra database server e client. Anche il tipo TIMESTAMP WITH LOCAL TIME ZONE rispetto a TIMESTAMP WITH TIME ZONE risparmia due byte. 

Cominciamo con un esempio, poi man mano descriverò i possibili problemi e le possibili soluzioni:

Creo una tabella con tre campi con i tre tipi timestamp: 

SQL> desc cristian
 Nome                                      Nullo?   Tipo
 ----------------------------------------- -------- ----------------------------
 A                                                  TIMESTAMP(6)
 B                                                  TIMESTAMP(6) WITH TIME ZONE
 C                                                  TIMESTAMP(6) WITH LOCAL TIME
                                                     ZONE

 La configurazione timezone è:

SQL> select dbtimezone,sessiontimezone from dual;

DBTIME
------
SESSIONTIMEZONE
--------------------------------------------------------------------------------
+02:00
+01:00

Qui c'è la prma anomalia:

SQL> select systimestamp,localtimestamp from dual;

SYSTIMESTAMP
---------------------------------------------------------------------------
LOCALTIMESTAMP
---------------------------------------------------------------------------
25-GEN-08 16:34:38,699599 +01:00
25-GEN-08 16:34:38,699607

 Il fatto che dbtimezone sia +2:00 dipende dal fatto che tale valore viene deciso alla creazione del database. Può essere specificato durante il comando create database, se non viene specificato viene preso dal sistema operativo.

Una prima fonte di informazioni al riguardo può essere la nota metalink 340512.1 "Timestamps & time zones - FAQ" ,il concetto che il dbtimezone viene definito alla creazione viene detto anche in questo thread del forum di discussione Oracle.

Nella FAQ, nelle risposte alle prime quattro domande spiega come il valore i dbtimezone sia poco rilevante, esso viene utilizzato per normalizzare i dati di tipo TIMESTAMP WITH LOCAL TIME ZONE (TSLTZ) , non cambia da solo, può essere cambiato con il comando:

ALTER DATABASE SET TIME_ZONE='XXX';

Ma solo se nel database non vi sono già campi di tipo TSLTZ, altrimenti , come descritto nella nota metalink 230099.1 "ORA-2231 or ORA-30079 When Trying to Change the Database Time Zone", Oracle da errore. 

il valore di SYSTIMESTAMP non dipende da DBTIMEZONE, viene prelevato dal sistema operativo.

Se non ho capito male, il modo per verificare l'effettivo timezone offset del database server dovrebbe essere :

SQL> select extract(timezone_hour from systimestamp) from dual;

EXTRACT(TIMEZONE_HOURFROMSYSTIMESTAMP)
--------------------------------------
                                     1
Nella FAQ mette in guardia dall'associare l'offset a una timezone, quindi spiega come sia corretto avere:

SQL> select extract(timezone_region from systimestamp) from dual;

EXTRACT(TIMEZONE_REGIONFROMSYSTIMESTAMP)
----------------------------------------------------------------
UNKNOWN

 

 La nota metalink 226692.1 "Finding out your NLS Setup" da qualche indicazione per verificare le impostazioni del database.

Se si usano driver JDBC attenzione alla nota 278062.1 "Current_Timestamp Comes As Gmt" in cui si spiega come nelle versioni dei driver tra la 9.2.0.4 e la 10.1 (io però ho avuto lo stesso problema con la versione 9.2.0.3, e non con la 10.2.0.1) non settando esplicitamente tramite un metodo la time zone il driver setta in automatico l'offset a +0:00.

 Ora, tornando  alla tabella di esempio sopra l'inconveniente che ho incontratoè il seguente: database server con timezone '+01:00' (Italia), client con JDBC bacato, quindi "virtualmente" con timezone '+0:00'.  Quiindi, per simulare:

SQL> alter session set time_zone='+0:00';

Modificata sessione.

SQL> update cristian set a=systimestamp,b=systimestamp,c=systimestamp;

Aggiornata 1 riga.

 SQL> select * from cristian;

A
---------------------------------------------------------------------------
B
---------------------------------------------------------------------------
C
---------------------------------------------------------------------------
25-GEN-08 17:32:05,051174
25-GEN-08 17:32:05,051174 +01:00
25-GEN-08 16:32:05,051174

Ed ecco il problema: 

SQL> select * from cristian where a<systimestamp;

Nessuna riga selezionata

SQL> select * from cristian where a<systimestamp;

Nessuna riga selezionata

Per farla breve dico subito  che sono andato a vedere su V$SQL_PLAN cosa accadeva ed ho trovato:

SYS_EXTRACT_UTC(INTERNAL_FUNCTION("A"))<SYS_EXTRACT_UTC(SYSTIMESTAMP(6)) 

Ora, purtroppo mi manca un passo, cioè cosè INTERNAL_FUNCTION ma in ogni caso quel che accade è che quando Oracle fa il confronto a<systimestamp, essendo a di tipo timestamp senza informazioni sulla time zone pensa bene di usare la timezone del client (nel nostro caso '+0:00'), systimestamp l'informazione sulla time zone ce l'ha, ed è '+01:00', quindi convertendo i due valori in UTC (ora universale di greenich) risulta che "a" è superiore a systimestamp.

Una possibile soluzione sta nell'uso della funzione FROM_TZ,

 SQL> select a,b,c,sys_extract_utc(a) from cristian where from_tz(a,dbtimezone)<systimestamp;

A
---------------------------------------------------------------------------
B
---------------------------------------------------------------------------
C
---------------------------------------------------------------------------
SYS_EXTRACT_UTC(A)
---------------------------------------------------------------------------
25-GEN-08 17:32:05,051174
25-GEN-08 17:32:05,051174 +01:00
25-GEN-08 16:32:05,051174
25-GEN-08 17:32:05,051174

La funzione FROM_TZ crea un tipo timestamp with time zone a partire da un timezone e una stringa che definisce la timezone, vi sono due valori particolari: dbtimezone e sessiontimezone .

In realtà la soluzione non è buona perche dbtimezone è sempre quel valore cablato e statico. 

SQL> select a,b,c from cristian where from_tz(a,to_Char('+0'||extract(timezone_hour from systimestam
p)||':00'))<systimestamp;

A
---------------------------------------------------------------------------
B
---------------------------------------------------------------------------
C
---------------------------------------------------------------------------
25-GEN-08 17:32:05,051174
25-GEN-08 17:32:05,051174 +01:00
25-GEN-08 18:32:05,051174