TIMESTAMP e TIME ZONE

lunedì 28 gennaio 2008 alle 28:53 | Pubblicato su SQL | 2 commenti

Recentemente ho riscontrato un problemino della nostra applicazione con il tipo dati TIMESTAMP, ciò mi ha indotto ad approfondire il tema, cercherò ora di riassumere un po’ le situazione ma non garantisco la chiarezza dell’esposizione.

Oracle prevede tre tipi dato “timestamp”:

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

il tipo TIMESTAMP WITH TIME ZONE utilizza due byte in più per memorizzare anche la time zone, il tipo timestamp non tratta la time zone il tipo TIMESTAMP WITH LOCAL TIME ZONE tiene conto sempre della time zone del client, lo fa normalizzando sempre il timestamp passato con un parametro fisso del database chiamato DBTIMEZONE. Il valore del parametro DBTIMEZONE è consultabile tramite la query:

SELECT DBTIMEZONE FROM DUAL.

Una fonte di informazioni molto esauriente al riguardo può essere la nota metalink 340512.1 “Timestamps & time zones – FAQ”

DBTIMEZONE un parametro relativamente poco importante, esso viene definito alla creazione del database (o esplicitamente con la CREATE DATABASE, oppure implicitamente derivandolo dal sistema operativo) dopo di ché se non cambiato esplicitamente con l’istruzione “ALTER DATABASE SET TIME_ZONE” non cambia più. Esso viene usato solo per la normalizzazione dei valori dei campi TIMESTAMP WITH LOCAL TIME ZONE e quindi quando nel database vi sono già tabelle con campi di questo tipo non è più possibile modificarlo, infatti provando a farlo si riceve l’errore ORA- 30079:

SQL> alter database set time_zone=’+1:00′;
alter database set time_zone=’+1:00′
*
ERROR at line 1:
ORA-30079: cannot alter database timezone when database has TIMESTAMP WITH
LOCAL TIME ZONE columns

Quindi quando una sessione inserisce un valore in un campo di tipo TIMESTAMP WITH LOCAL TIME ZONE oracle “normalizza il valore, ovvero calcola la differenza tra la timezone del client (SESSIONTIMEZONE) e la DBTIMEZONE e sottrae tale valore al timestamp prima di memorizzarlo. Quindi se si setta DBTIMEZONE a ‘+0:00’ come suggerito nella nota metalink, l’ora viene salvata come GMT o UTC ovvero secondo il fuso di Greenich. Quando il client richiede il valore oracle fa l’operazione inversa, quindi mostra sempre il valore normalizzato all’ora locale. Interessante secondo il fatto che tale consistenza rimane anche facendo degli export e import tra database con settaggi diversi, cioè se quando viene fatto l’import il database di destinazione ha un diverso DBTIMEZONE Oracle rinormalizza il dato. Quindi il valore di DBTIMEZONE per l’utente non è di alcun interesse.

La cosa che secondo me può generare più confusione è il non corretto utilizzo delle funzioni di sistema LOCALTIMESTAMP, CURRENT_TIMESTAMP, SYSTIMESTAMP.

LOCALTIMESTAMP ritorna l’ora del database server come tipo timestamp (quindi senza informazioni sulla time zone) ma normalizzandolo con la timezone del client. CURRENT_TIMESTAMP fa lo stesso ma il tipo che ritorna è TIMESTAMP WITH TIME ZONE; SYSTIMESTAMP restituisce l’ora del database server con la time zone del server.

Quindi vediamo un esempio:

SQL> alter session set time_zone=’+00:00′;

Session altered.

SQL> select sessiontimezone,dbtimezone from dual;

SESSIONTIMEZONE
—————————————————————————
DBTIME
——
+00:00
+00:00

SQL> select current_timestamp,localtimestamp,systimestamp from dual;

CURRENT_TIMESTAMP
—————————————————————————
LOCALTIMESTAMP
—————————————————————————
SYSTIMESTAMP
—————————————————————————
28-JAN-08 01.57.23.454246 PM +00:00
28-JAN-08 01.57.23.454246 PM
28-JAN-08 02.57.23.454239 PM +01:00

In questo caso DBTIMEZONE è settato a ‘+00:00’ ma la time zone del server è ‘+01:00’ come visualizzato dal campo SYSTIMESTAMP e questo pare essere l’unico modo per verificare la time zone reale del server. In realtà è più corretto parlare di time zone offset, infatti questo può cambiare anche in seguito all’ora legale. Infatti diversi nostri server hanno DBTIMEZONE ‘+02:00’ in quanto sono stati creati in periodo di ora legale senza specificare nulla, in tal caso Oracle ricava l’offset dal sistema operativo.

Ora vediamo un esempio di come si può generare un comportamento strano:

Ho un tabella così fatta:

SQL> desc cristian
Name Null? Type
—————————————– ——– —————————-
A TIMESTAMP(6)
B TIMESTAMP(6) WITH TIME ZONE
C TIMESTAMP(6) WITH LOCAL TIME
ZONE
SQL> insert into cristian (a) values (systimestamp);

1 row created.

SQL> select * from cristian where a<systimestamp;

no rows selected

SQL> select a,systimestamp from cristian;

A
—————————————————————————
SYSTIMESTAMP
—————————————————————————
28-JAN-08 03.35.01.900653 PM
28-JAN-08 03.35.39.463621 PM +01:00

La prima volta che sono riuscito a riprodurre questo problema non ho capito cosa accadesse, fino a quando non sono andato a vedere l’execution plan su V$SQL_PLAN, da li si capisce che quando Oracle fa il confronto tra il valore del campo “a” e systemestamp converte implicitamente il valore di a al tipo TIMESTAMP WITH TIME ZONE usando però la time zone del client.

SYS_EXTRACT_UTC(INTERNAL_FUNCTION(“A”))<SYS_EXTRACT_UTC(SYSTIMESTAMP(6))

La funzione SYS_EXTRACT_UTC prende in input un TIMESTAMP WITH TIME ZONE e restituisce un TIMESTAMP contenente il valore di input normalizzato secondo il fuso di Greenich (ciè ritorna l’ora di greenech per l’ora di input). Ora, la funzione INTERNAL_FUNCTION non so cosa sia ma senz’altro è qui che Oracle ha bisogno di convertire A da TIMESTAMP a TIMESTAMP WITH TIME ZONE.

durante l’inserimento Oracle pare usi la timezone del server,  e facendo un analogo test con LOCALTIMESTAMP tutto funziona (nota 08/04/2009: invece in fase di interrogazione usa quella della sessione, quindi del client).

L’utilizzo di LOCALTIMESTAMP però secondo me è soggetto al problema che se insert e select sono fatti due client con time zone diversi non funziona un tubo. Ostinandosi a voler usare sempre un campo di tipo TIMESTAMP per fare simili operazioni l’unico modo affidabile per fare il controllo è basarsi sempre sulla time zone del server, cosa non semplice, a quanto pare l’unico modo che ho trovato si basa sull’uso delle funzioni FROM_TZ ed EXTRACT:

from_tz(<CAMPODITIPOTIMESTAMP>,to_Char(‘+0’||extract(timezone_hour from systimestamp)||’:00′))

La funzione FROM_TZ prende in input un TIMESTAMP e una stringa che definisce la time zone e restituisce un TIMESTAMP WITH TIME ZONE.

La funzione EXTRACT estrae in questo caso l’offset (in ore) da SYSTIMESTAMP, quindi quello effettivo sul sistema operativo del database server.

La cosa è a prova di client con time zone diversi ma non è proprio la soluzione ideale.

Tutto ciò è nato a causa di un baco dei driver JDBC, infatti nella nota 278062.1 “Current_Timestamp Comes As Gmt” 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 che nel caso dell’Italia crea una differenza dal server.

2 commenti »

RSS feed for comments on this post. TrackBack URI

  1. […] volta che devo ripassare le caratteristiche dei vari tipi TIMESTAMP di Oracle vado a rileggermi questo post, che oggi a dire il vero ho notato non essere chiarissimo e che presto spero di […]

  2. […] sono cose che ho gia riassunto in un mio vecchio post e altro. Alla fine pare che la Time Zone del server influisca solo sulle funzioni SYSDATE e […]


Lascia un commento

Inserisci i tuoi dati qui sotto o clicca su un'icona per effettuare l'accesso:

Logo WordPress.com

Stai commentando usando il tuo account WordPress.com. Chiudi sessione / Modifica )

Foto Twitter

Stai commentando usando il tuo account Twitter. Chiudi sessione / Modifica )

Foto di Facebook

Stai commentando usando il tuo account Facebook. Chiudi sessione / Modifica )

Google+ photo

Stai commentando usando il tuo account Google+. Chiudi sessione / Modifica )

Connessione a %s...

Blog su WordPress.com.
Entries e commenti feeds.

%d blogger cliccano Mi Piace per questo: