21. September 2009

DATE und TIMESTAMP in SQL und PL/SQL: Wissenswertes ...

English title: DATE and TIMESTAMP in SQL and PLSQL: Things to know ...

Heute möchte ich einige Fragen und Antworten zum Thema "Umgang mit Datumswerten" zusammenfassen. Man hat ja immer wieder damit zu tun. Zunächst stellt sich ja recht häufig die Frage, ob man DATE oder TIMESTAMP verwenden sollte. TIMESTAMP ist ja der "jüngere" und mächtigere Datentyp. Andererseits kommt es immer noch häufig vor, dass Werkzeuge nicht richtig mit TIMESTAMP umgehen können. Aus diesem Grund wird DATE immer noch recht häufig (selbst auf 10g oder 11g-Datenbanken) genutzt. Das ist jedoch erstmal kein Problem - solange man keine speziellen "Features" des TIMESTAMP benötigt, sind beide Typen gleichwertig. Zunächst sollte man also diese Featues des TIMESTAMP herausarbeiten.
  • Die kleinste Einheit eines DATE ist die Sekunde, ein TIMESTAMP kommt auf die Millisekunde herunter.
  • TIMESTAMP kann mit Zeitzonen umgehen - es gibt den Datentypen TIMESTAMP WITH TIME ZONE und SQL-Funktionen wie FROM_TZ, die recht hilfreich beim Konvertieren zwischen Zeitzonen sind.
Today I'd like to post brief summary about the handling of datetime values within the Oracle database. This is a topic which is relevant for every developer. The first question is whether to use DATE or TIMESTAMP. TIMESTAMP is the more recent datatype for datetime values with more functionality. But there are many tools or programs which cannot handle TIMESTAMP values properly - therefore DATE is still more often being used (even on 10g or 11g systems). But this is not really a problem - DATE might be as suitable as TIMESTAMP. First we should work out the differences between DATE and TIMESTAMP, or expressed otherwise: What are the "features" of the TIMESTAMP datatype?
  • The smallest unit of a DATE value is a second. TIMESTAMP instead handles Milliseconds.
  • TIMESTAMP can handle time zones. There is timezone-aware variant: TIMESTAMP WITH TIME ZONE. And there are SQL functions like FROM_TZ which are useful for converting datetime values between timezones.
Weder DATE noch TIMESTAMP speichern das Datum intern als Zahl (wie Unix es macht). Das kann man mit der DUMP-Funktion auch einfach selbst nachsehen (Man sieht aber bereits an den Bytes, dass TIMESTAMP intern mit UTC arbeitet):
Neither DATE nor TIMESTAMP store their value internally as a NUMBER. This can be easily observed as follows:
select sysdate, dump(sysdate) bytes from dual
/

SYSDATE             BYTES
------------------- ----------------------------------------
21.09.2009 12:13:50 Typ=13 Len=8: 217,7,9,21,12,13,50,0

select systimestamp, dump(systimestamp) bytes from dual
/

SYSTIMESTAMP                        BYTES
----------------------------------- ------------------------------------------------------------------------------
21.09.2009 12:15:21,413578 +02:00   Typ=188 Len=20: 217,7,9,21,10,15,21,0,16,179,166,24,2,0,5,0,0,0,0,0
"217,7" wird hexadezimal als "D9","07" ausgedrückt. Als 16bit-Zahl wäre das "07D9" und das wiederum ist in dezimaler Schreibweise 2009. Man sieht also, dass Jahre, Monate, Tage, Stunden, Minuten und Sekunden explizit im DATE bzw. TIMESTAMP-Wert drinstecken.
The hexadecimal notation for "217,7" is "D9","07". This as a 16bit number would be "07D9" and this converted back to decimal is "2009". We see that TIMESTAMP and DATE store the individual information for years, months, days, hours, minutes and seconds.
DATE und TIMESTAMP erlauben Datumsarithmetik. Und beide erlauben das Arbeiten mit numerischen Werten und mit Intervall-Datentypen. Die meisten von euch arbeiten sicherlich mit numerischen Werten: Dabei ist die "Einheit", mit der man arbeitet, 1 Tag. Ein "DATE" ist also "ein Tag" (für TIMESTAMP gilt das gleiche). Eine Stunde ist demnach 1/24 DATE, eine Minute ist 1/1440 DATE und so weiter. Möchte man also wissen, welche Zeitspanne zwischen zwei DATE-Werten liegt, so zieht man sie zunächst mal voneinander ab ...
DATE allows datetime arithmetic as well as TIMESTAMP. One can work with numeric values as well as with interval types. The latter ones are (as we will see) more recommended. The "Unit" of DATE and TIMESTAMP is a "day", so "one DATE" means "one day". One hour is therefore 1/24 "DATE" and a minute is 1/1440 "DATE". If you like to know the datetime interval between two DATE values you just subtract one from the other.
select 
  to_date('2009-09-21 14:45', 'YYYY-MM-DD HH24:MI:SS') -
  to_date('2009-07-21 07:30', 'YYYY-MM-DD HH24:MI:SS') DATETIME_INTERVAL
from dual
/

DATETIME_INTERVAL
-----------------
     62,302083333
Bei DATE-Werten wird das Ergebnis in einen numerischen Wert konvertiert. Die Einheit ist "DATE", also Tage. Es kommen also 62 und ein knapper "Drittel-Tag" heraus. Verwendet man TIMESTAMP, so bekommt man sofort einen Intervall-Datentypen (hier: Day-To-Second) ...
When using the DATE datatype, the result is being converted into a NUMBER value; the UNIT is (as seen above) "days". So this result means: a bit more then 62 days. When using the TIMESTAMP datatype, the result is being expressed as an interval data type (here: Day-To-Second).
select 
  to_timestamp('2009-09-21 14:45', 'YYYY-MM-DD HH24:MI:SS') -
  to_timestamp('2009-07-21 07:30', 'YYYY-MM-DD HH24:MI:SS') DATETIME_INTERVAL
from dual
/

DATETIME_INTERVAL
------------------------------------------------
+000000062 07:15:00.000000000
Den numerischen Wert kann man allerdings auch in einen Intervall-Datentypen konvertieren ...
You can explicitly convert the NUMBER value into an interval datatype ...
select 
  numtodsinterval(
    to_date('2009-09-21 14:45', 'YYYY-MM-DD HH24:MI:SS') -
    to_date('2009-07-21 07:30', 'YYYY-MM-DD HH24:MI:SS') 
   ,'DAY'
  ) DATETIME_INTERVAL
from dual
/

DATETIME_INTERVAL
-----------------------------------------------
+000000062 07:14:59.999999999
... allerdings zeigt sich hier ein kleines Rundungsproblem. Besser wäre es also, sich das Ergebnis der Datumsarithmetik auch bei Verwendung von DATE direkt als Intervallwert zurückgeben zu lassen. So vermeidet man den Umweg über NUMBER:
... but there is a rounding problem now. It would be much better to omit this roundtrip to the NUMBER value. The following syntax retrieves the result of the datetime arithmetic directly as an interval datatype - The DATE subtraction now returns the same result as the TIMESTAMP subtraction.
select 
 (
   to_date('2009-09-21 14:45', 'YYYY-MM-DD HH24:MI:SS') -
   to_date('2009-07-21 07:30', 'YYYY-MM-DD HH24:MI:SS')
 ) day(9) to second DATETIME_INTERVAL
from dual
/

DATETIME_INTERVAL
------------------------------------------
+000000062 07:15:00.000000000
Man kommt also sowohl mit DATE als auch mit TIMESTAMP zu einem in SQL*Plus gut lesbaren Ergebnis. Aber man arbeitet ja nicht immer in SQL*Plus und manchmal benötigt man eben strukturierten Zugriff auf die einzelnen Elemente: Mit der EXTRACT-Funktion kommt man an die einzelnen Elemente des Intervalls heran:
So in SQL*Plus we can retrieve the results in a readable format - for DATE as well as for TIMESTAMP values. But application developers often need structured access to the information in order to retrieve the individual information elements. The EXTRACT function allows this:
select 
  extract(
    HOUR from
    (
      to_date('2009-09-21 14:45', 'YYYY-MM-DD HH24:MI:SS') -
      to_date('2009-07-21 07:30', 'YYYY-MM-DD HH24:MI:SS')
    ) day(9) to second
  ) INTERVAL_HOURS
from dual
/

INTERVAL_HOURS
--------------
             7
Minuten gehen dann analog ...
A similiar call extracts the Minutes ...
select 
  extract(
    MINUTE from
    (
      to_date('2009-09-21 14:45', 'YYYY-MM-DD HH24:MI:SS') -
      to_date('2009-07-21 07:30', 'YYYY-MM-DD HH24:MI:SS')
    ) day(9) to second
  ) INTERVAL_MINUTES
from dual
/

INTERVAL_MINUTES
----------------
              15
Geht es ums Addieren oder Subtrahieren von einem vorhandenen Zeitstempel, so bieten sich auch wieder die numerischen Datentypen als auch Intervall-Datentypen an. Wir möchten 12 Stunden zu einem Datum addieren ... Erstmal numerisch ...
When it's about adding or subtracting from a datetime value, there is -again- the choice between using numeric (fractional) or interval values. So if we'd like to add 12 hours to a datetime value we could use the fractional method ...
select 
  to_date('2009-09-21 14:45', 'YYYY-MM-DD HH24:MI:SS') + (12/24) NEW_TIME
from dual
/

NEW_TIME
-------------------
22.09.2009 02:45:00

select 
  to_timestamp('2009-09-21 14:45', 'YYYY-MM-DD HH24:MI:SS') + (12/24) NEW_TIME
from dual
/

NEW_TIME
-------------------
22.09.2009 02:45:00
... welche sowohl mit DATE als auch mit TIMESTAMP funktioniert. Aber Achtung: In letzterem Fall hat die Datenbank implizit in ein DATE konvertiert. Und wenn man mit der DUMP-Funktion nachsieht, stellt man fest, dass man auch einen DATE zurückbekommt ... Außerdem kann man sich nun schon denken, dass es hier Rundungsdifferenzen geben kann, besonders wenn man anfängt, einzelne Minuten oder Sekunden zu addieren oder abzuziehen. Besser sind also die Intervall-Datentypen:
... which works with DATE as well as with TIMESTAMP. But in the latter case there was an implicit conversion from TIMESTAMP to DATE (check with the DUMP function). And when it's about adding minutes or seconds it's obvious that we'll run into rounding issues again. So adding interval values is the better choice.
select 
  to_date('2009-09-21 14:45', 'YYYY-MM-DD HH24:MI:SS') + interval '12' hour NEW_TIME
from dual
/

NEW_TIME
-----------------------------------
22.09.09 02:45:00,000000000
Das geht übrigens auch in PL/SQL - allerdings kann man, möchte man die Anzahl Stunden dynamisch gestalten, dieses SELECT nicht genauso in einem PL/SQL-Block verwenden. Man muss hier ein wenig anders arbeiten:
This also works within PL/SQL - but if you like to have the amount of hours in a variable you cannot use the above function call. In PL/SQL, the code must be a bit different:
declare
  v_zeit timestamp; 
  v_hrs  interval day(9) to second;
begin
  -- 0 days, 12 hours, 0 minutes, 0 seconds
  v_hrs := '0 12:00:00'; 
  v_zeit := to_timestamp('2009-09-21 14:45', 'YYYY-MM-DD HH24:MI:SS') + v_hrs;
  dbms_output.put_line(v_zeit);
end;
/
Intervalle können auch einfach aus Zahlenwerten heraus berechnet werden. Möchte man bspw. wissen, was 2,65 Tage genau sind, dann lässt sich das wie folgt ermitteln:
You can also determine interval values from numeric (fractional) values. If you like to know what 2.65 days exactly mean, try the following:
select
 numtodsinterval(2.65, 'DAY') interval
from dual
/

INTERVAL
----------------------------------------------------
+000000002 15:36:00.000000000
Die Funktion lässt sich noch anders nutzen: Angenommen, es sind keine 2,65 Tage, sondern 2,65 Stunden gemeint. Auch das lässt sich interpretieren ...
Lets assume that these are not 2.65 days but 2.65 hours. What does that mean ...?
select
 numtodsinterval(2.65, 'HOUR') interval
from dual
/

INTERVAL
----------------------------------------------------
+000000000 02:39:00.000000000
Passend dazu gibt es neben dem Day-To-Second-Intervall auch das Year-To-Month-Intervall. Angenommen, wie haben 4,55 Jahre: Was bedeutet das eigentlich ...?
Day-To-Second is not the only interval datatype: There is also Year-To-Month. So we have 4.55 Years: What does that mean ...?
select
 numtoyminterval(4.55, 'YEAR')     intervall
from dual
/

INTERVALL
----------------------------------------------------
+000000004-06
Also 4 Jahre und 6 Monate. Nun wenden wir das auf ein Beispiel mit zwei Datumswerten an:
4 Years, 6 Months. We use this example again to interpret the interval between to DATE values.
select 
 (
   to_date('2009-09-21 14:45', 'YYYY-MM-DD HH24:MI:SS') -
   to_date('2007-07-21 07:30', 'YYYY-MM-DD HH24:MI:SS')
 ) year(9) to month DATETIME_INTERVAL
from dual
/

DATETIME_INTERVAL
-------------------
+000000002-02
Nun wäre es ja schön, wenn man das kombinieren könnte. Wir möchten also wissen, wieviele Jahre, Monate, Tage, Stunden, Minuten und Sekunden zwischen zwei Datumswerten vergangen sind. Wie haben den Intervalltypen Year-To-Month für Jahre und Monate und Day-To-Second für Tage bis Sekunden. Dies gilt es nun zu kombinieren: Dazu ermittelt man zunächst das Intervall Year-To-Month, zieht dieses vom späteren "Enddatum" ab und ermittelt dann vom Rest das Intervall Day-To-Second.
Now, as the last step we'd like to combine the two: We want to know how many years, months, days, hours, minutes and seconds are between two datetime values. For years and months we use the Year-To-Month interval type, we subtract this from the least datetime value and finally we use the Day-To-Second interval for the remainder. Check out the following PL/SQL code.
declare
  v_enddate   date := to_date('2009-09-21 14:45', 'YYYY-MM-DD HH24:MI:SS');
  v_startdate date := to_date('2007-07-15 07:30', 'YYYY-MM-DD HH24:MI:SS');
  v_ym        interval year(9) to month;
  v_ds        interval day(9) to second;
begin
  -- Jahre und Monate ermitteln
  v_ym := (v_enddate - v_startdate) year to month;
  -- Jahr und Monate vom Enddatum abziehen
  v_enddate := v_enddate - v_ym;
  -- Tage, Stunden, Minuten und Sekunden ermitteln
  v_ds := (v_enddate - v_startdate) day(9) to second;

  dbms_output.put_line('Years:   '||extract(YEAR from v_ym));
  dbms_output.put_line('Months:  '||extract(MONTH from v_ym));
  dbms_output.put_line('Days:    '||extract(DAY from v_ds));
  dbms_output.put_line('Hours:   '||extract(HOUR from v_ds));
  dbms_output.put_line('Minutes: '||extract(MINUTE from v_ds));
  dbms_output.put_line('Seconds: '||extract(SECOND from v_ds));
end;
/

Years:   2
Months:  2
Days:    6
Hours:   7
Minutes: 15
Seconds: 0
Ich denke, das wäre es dann für heute; wie man mit TIMESTAMP und Zeitzonen umgehen kann, verschiebe ich aufs nächste Blog-Posting.
This should be enough datetime blogging for today ... how to handle timezones will then be topic of the next blog posting.

10. September 2009

Erweiterungen für den SQL Developer: APEX, XML DB, Dateisystem und mehr ...

English title: APEX and XML DB extensions for SQL Developer

In den letzten Tagen habe ich noch ein wenig mit den Erweiterungsmöglichkeiten des SQL Developer gespielt. Die Erweiterungen, die dabei herausgekommen sind, möchte ich euch nicht vorenthalten ...
During the last few days I played around with SQL Developer extensibility. I produced some extensions which I'd like to share ...
Der SQL Developer ist von Haus aus erweiterbar - das wissen die meisten. Nicht so bekannt ist die Tatsache, dass man dazu nicht unbedingt Java programmieren muss. Vor allem SQL und PL/SQL-gestützte Erweiterungen können auch mit XML-Dateien, die dann einfach in den SQL Developer eingeklinkt werden, realisiert werden. Wie man das macht und wie man die XML-Dateien erstellt, ist unter anderem in diesen Artikeln beschrieben:
SQL Developer is extensible by default - this is known widely. But only a few know that it is not required to implement java code for this. Database-centric (SQL and PL/SQL-based) extensions can be implemented in a declarative manner with by XML files. The following articles describe the process.
Ladet einach mal das ZIP-Archiv herunter und probiert es mal aus - Einfach im SQL Developer zu Extras, Voreinstellungen und dort zu Datenbank und Bnutzerdefinierte Erweiterungen navigieren. Dort fügt Ihr die Dateien aus dem ZIP-Archiv hinzu. Der Dateiname enthält einen Hinweis, ob die Datei als NAVIGATOR, EDITOR oder ACTION zu registrieren ist. Anschließend SQL Developer neu starten und ansehen ...
Just download the zip archive and try the extensions - just navigate in SQL Developer to Tools then to preferences. In the preference window go to database and user defined extensions. Register the files in the downloaded ZIP archive there. The filename contains a hint whether to register the file as NAVIGATOR, EDITOR or ACTION. After that, restart SQL Developer and have a look ...
  • APEX Workspace Management Extension
    Im Navigationsbaum des SQL Developer findet Ihr anschließend den Eintrag APEX Workspaces; Ihr müsst aber als SYS verbunden sein. Die Erweiterung habe ich mit APEX 3.1 und APEX 3.2 getestet.
  • Extension für Package FILE_TYPE
    Dies ist eine Erweiterung für das Package zum Zugriff auf das Server-Dateisystem.
  • Nutzer der Oracle XML DB finden auch eine Erweiterung zur Steuerung des XML DB Repository
  • Wer das Skript zum Bestimmen der tatsächlichen Größe einer Tabelle nutzt, findet im Archiv auch eine Erweiterung hierfür.
  • APEX Workspace Management Extension
    The navigation tree contains now an entry APEX Workspaces; you have to be connected as SYS. I tested this extension with APEX 3.1 and APEX 3.2. Note that there are also some features in the navigator tree entries' context menus.
  • Extension für Package FILE_TYPE
    This is an extension for my package for retrieving directory tree listings and file contents.. It's basically a server filesystem viewer. Read the packages' install notes before using this.
  • Oracle XML DB users might find the extension for managing the XML DB repository useful.
  • Users of the SQL script to determine the actual size of a table can embed this into SQL Developer with the fourth contained extension.
Viel Spaß beim Ausprobieren - und gebt mir Feedback!
Have fun trying it - and tell me if you like it (or not).

1. September 2009

Oracle11g Release 2 ist freigegeben!

English title: Hot News: Oracle11g Release 2 is available

Oracle 11g Release 2 ist verfügbar. Für Linux kann die neue Datenbankversion aus dem Oracle Technet heruntergeladen werden.
Oracle 11g Release 2 is available. You can download the linux version from OTN.
Eine Übersicht über interessante neue Features findet sich für Entwickler in der APEX Community, für DBAs in der DBA Community. Schaut einfach mal rein.
Want to know what's new? Have a look into the Oracle11g Release 2 microsite.

Beliebte Postings