Mit DATE oder TIMESTAMP Datentypen wird ständig gearbeitet; sie kommen in jedem Datenmodell vor und folglich muss jede APEX-Anwendung damit umgehen können. Dieser Tipp enthält einige Informationen zum Umgang mit diesen Datentypen - und worauf man als APEX-Entwickler achten sollte.

DATE oder TIMESTAMP?

Die Oracle-Datenbank bietet vier Datentypen zum Speichern von Zeitstempeln an:

• DATE ist der älteste Datentyp zum Speichern von Zeitstempeln in der Datenbank. Anders als der Name nahelegt, speichert DATE immer sowohl das Datum als auch die Uhrzeit ab. Intern werden ganz konkrete Werte für Jahr, Monat, Tag, Stunde, Minute und Sekunde abgelegt. Die SQL-Funktion SYSDATE gibt den aktuellen Zeitstempel zurück.

  SQL> alter session set nls_date_format='YYYY-MM-DD HH24:MI:SS';
  
  Session geändert.
  
  SQL> select sysdate, dump(sysdate) as date_bytes from dual;
  
  SYSDATE              DATE_BYTES                           
  -------------------- -----------------------------------
  2017-11-23 23:41:08  Typ=13 Len=8: 225,7,11,23,23,41,8,0  
  

• TIMESTAMP erweitert den DATE-Datentypen um Sekundenbruchteile. Das interne Speicherformat für den TIMESTAMP sieht auch die Zeitzone vor, um diese aktiv nutzen zu können, braucht es jedoch die nachfolgend erläuterten Datentypen TIMESTAMP WITH TIME ZONE und TIMESTAMP WITH LOCAL TIME ZONE. Wie man am folgenden Beispiel sehen kann, liefert LOCALTIMESTAMP den aktuellen Zeitstempel als TIMESTAMP-Datentyp zurück.

  SQL> alter session set nls_timestamp_format='YYYY-MM-DD HH24:MI:SS.FF6';
  
  Session geändert.
  
  SQL> select localtimestamp, dump(localtimestamp) ts_bytes from dual;
  
  LOCALTIMESTAMP              TS_BYTES                                                               
  --------------------------- ---------------------------------------------------------------------
  2017-11-24 08:45:41.434175  Typ=187 Len=20: 225,7,11,24,8,45,41,0,24,252,224,25,1,0,3,0,127,1,0,0
  

• TIMESTAMP WITH TIME ZONE unterstützt die Zeitzone, und zwar explizit. Die Zeitzonen-Information wird beim Erzeugen einer Instanz explizit übergeben und in der Ausgabe sollte sie stets enthalten sein (richtige Formatmaske verwenden). Gibt man einen TIMESTAMP WITH TIME ZONE ohne Zeitzonen-Information aus, so ist die Information unvollständig.
  Es finden keine implizite Umrechnungen zwischen Zeitzonen statt; dem Entwickler stehen SQL-Funktionen dafür zur Verfügung. Instanz. Um den aktuellen Zeitstempel zu bekommen, verwendet man die SQL-Funktionen SYSTIMESTAMP oder CURRENT_TIMESTAMP. Ersterer liefert die Systemzeit in der Datenbank-Zeitzone, letzterer in der Session Zeitzone zurück. Im folgenden ein Beispiel für SYSTIMESTAMP.

  SQL> alter session set nls_timestamp_tz_format='YYYY-MM-DD HH24:MI:SS.FF6 TZR';
  
  Session geändert.
  
  SQL> select systimestamp from dual;
  
  SYSTIMESTAMP
  ----------------------------------
  2017-11-23 23:57:04.609608 -08:00
  

• TIMESTAMP WITH LOCAL TIME ZONE unterstützt die Zeitzone implizit. Eine Instanz wird ohne Angabe einer Zeitzone erzeugt; es wird die Session-Zeitzone angenommen; die Ausgabe erfolgt ebenfalls in der Session Zeitzone. Oracle-Intern werden die Instanzen auf die Datenbank-Zeitzone normalisiert.
  Das folgende Beispiel zeigt, wie TIMESTAMP WITH LOCAL TIME ZONE funktioniert. Zuerst wird eine Tabelle erzeugt und darin der aktuelle Zeitstempel gespeichert. SYSTIMESTAMP liefert zwar einen TIMESTAMP WITH TIME ZONE zurück, dieser wird aber automatisch auf TIMESTAMP WITH LOCAL TIME ZONE konviertiert. Wird die Tabelle danach selektiert, so wird stets auf die aktuelle Session-Zeitzone umgerechnet.

  SQL> create table zeitstempel( ts timestamp with local time zone );
  
  Table created.
  
  SQL> insert into zeitstempel values ( systimestamp );
  
  1 row created.
  
  SQL> alter session set time_zone='Europe/Berlin';
  
  Session altered.
  
  SQL> select * from zeitstempel;
  
  TS
  ---------------------------------------------------------------------------
  2017-11-24 12:55:39.761283
  
  SQL> alter session set time_zone='EST';
  
  Session altered.
  
  SQL> select * from zeitstempel;
  
  TS
  ---------------------------------------------------------------------------
  2017-11-24 06:55:39.761283
  

Formatmasken

Ob man mit einer Anwendung oder einem Werkzeug wie SQL Developer oder SQLPlus arbeitet; jede Ausgabe einer DATE- oder TIMESTAMP Instanz ist eine Konvertierung nach VARCHAR2. Die macht man entweder mit TO_CHAR explizit - oder die Datenbank macht es implizit. Und hierfür sind die NLS-Datumsformatmasken wichtig. Für die verschiedenen Datentypen gibt es verschiedene NLS-Parameter für die Default-Formatmaske.

• NLS_DATE_FORMAT für DATE
• NLS_TIMESTAMP_FORMAT für TIMESTAMP und TIMESTAMP WITH LOCAL TIME ZONE
• NLS_TIMESTAMP_TZ_FORMAT für TIMESTAMP WITH TIME ZONE

Bei einer expliziten Konvertierung mit TO_CHAR kann man die Formatmaske als zweiten Parameter mitgeben - ebenso beim Konvertieren eines VARCHAR in ein DATE oder TIMESTAMP mit TO_DATE, TO_TIMESTAMP oder TO_TIMESTAMP_TZ. In der Oracle-Dokumentation (SQL Reference) findet man alle Details zu den Formatmasken.

SQL> select to_char( sysdate, 'DD/MM/YYYY HH24-MI-SS' ) as sysdate_format from dual;

SYSDATE_FORMAT       
-------------------
24/11/2017 00-05-11 

SQL> select to_date( '01/01/2000 08-00-00', 'DD/MM/YYYY HH24-MI-SS' ) as mydate from dual;

MYDATE
-------------------        
2000-01-01 08:00:00  

Beim letzten Kommando finden zwei Konvertierungen statt! Zuerst wurde der String 01/01/2000 08-00-00 in eine DATE Instanz konvertiert; dazu wurde die Formatmaske mitgegeben; anschließend wurde für die Ausgabe (implizit) wieder nach VARCHAR2 zurückkonvertiert; hier findet die Session-Formatmaske (NLS_DATE_FORMAT) Anwendung. Beim Arbeiten und vor allem beim Ausgaben von DATE oder TIMESTAMP sollte man sich dieser ständig stattfindenden Konvertierungen stets bewusst sein.

Wird beim Erzeugen von DATE oder TIMESTAMP Instanzen mit TO_DATE oder TO_TIMESTAMP keine explizite Formatmaske verwendet, so nimmt Oracle die Standard-Formatmaske aus der Session. Diese richtet sich allerdings nach der Sprache, ist also nicht stabil. Meist ist es am besten, eine Formatmaske mitzugeben.

Ein Spezialfall ist das sog. DATE Literal; auf die folgende Art und Weise lässt sich eine DATE Instanz erzeugen - ganz ohne NLS-Formatmaske und unabhängig von der NLS-Einstellung der Datenbanksession (allerdings kann keine Uhrzeit übergeben werden).

SQL> select DATE'2017-08-01' from dual;

DATE'2017-08-01'    
------------------- 
01.08.2017 00:00:00  

Der DATE-Datentyp speichert die Uhrzeit ab. Speichert man in einer Tabelle das Tagesdatum (mit SYSDATE ermittelt), so ist die Uhrzeit stets enthalten. Dieses Verhalten, im Zusammenspiel mit einer Formatmaske, die keine Uhrzeit enthält, kann zu verwirrendem Verhalten führen ...

SQL> alter session set nls_date_format='DD-MON-RR';

Session altered.

SQL> create table meindatum (datum date);
SQL> insert into meindatum (datum) values (sysdate);

1 row inserted.

SQL> select * from meindatum;

DATUM      
---------
24-NOV-17  

1 row selected.

SQL> select * from meindatum where datum = to_date('24-NOV-17');

no rows selected.

SQL> select * from meindatum where datum = to_date('24-NOV-17', 'DD-MON-RR');

no rows selected.

Das Ändern der Datumsformatmaske macht es sofort klar.

SQL> alter session set nls_date_format='DD-MON-RR HH24.MI.SS';

Session altered.

SQL> select * from meindatum;

DATUM               
------------------
24-NOV-17 00.16.19  

1 row selected.

SQL> select to_date('24-NOV-17', 'DD-MON-RR') from dual;

TO_DATE('24-NOV-17','DD-MON-RR')  
--------------------------------
24-NOV-17 00.00.00                

Man sieht sofort, dass die Datumsinstanzen nicht gleich sind. Wenn man nur das Datum braucht, sollte man die Uhrzeit vor dem Speichern mit der SQL-Funktion TRUNC entfernen.

SQL> select sysdate, trunc(sysdate) from dual;

SYSDATE             TRUNC(SYSDATE)      
------------------- ----------------------
24-NOV-17 02.36.30  24-NOV-17 00.00.00  

Und Application Express ...?

Alles bis hierher geschriebene gilt uneingeschränkt auch für Application Express. Wann immer man eine DATE oder TIMESTAMP Spalte in einer APEX-Komponente verwendet, folgt die Darstellung dem beschriebenen Verhalten. Während man die Standardformatmaske für DATE oder TIMESTAMP in SQL Plus mit dem ALTER SESSION Kommando ändert, stellt man sie für eine APEX-Anwendung in den Anwendungsattributen ein. Navigieren Sie dazu zu den Shared Components dort zu den Application Definition Attributes und dann zu Globalization.

Datumsformatmasken für die Anwendung einstellen - Shared Components

Die hier eingestellten Formatmasken Application Date Format, Application Timestamp Format und Application Timestamp Time Zone Format werden von Application Express genutzt, um die NLS-Session Parameter NLS_DATE_FORMAT, NLS_TIMESTAMP_FORMAT und NLS_TIMESTAMP_TZ_FORMAT für diese Anwendung zu ändern. Ist nichts eingestellt, so wirken die Defaults der Oracle-Datenbank. Eine Sonderrolle nimmt das Application Date Time Format ein; es hat keine Auswirkung auf einen NLS-Parameter und wirkt nur an ausgewählten Stellen in Application Express.

Wo immer also ein DATE oder TIMESTAMP verwendet wird, richtet sich das Datumsformat nach den hier gemachten Einstellungen, es sei denn, eine explizite Formatmaske wird genutzt. An allen möglichen Stellen, bspw. Berichtsspalten oder Date Pickers, können explizite Formatmasken hinterlegt werden; diese gehen dem Default dann vor.

Eine besondere Formatmaske, die es nur in Application Express gibt, ist SINCE. Bereits im Application Builder ist sie an zahlreichen Stellen im Einsatz.

Die SINCE Formatmaske in Aktion

SINCE sorgt dafür, dass das Datum als Text ausgegeben wird, der angibt, wie lange der Zeitstempel zurückliegt. Die Sprache richtet sich nach der Anwendungssprache; und es sind die Sprachen verfügbar, für welche Übersetzungen des Application Builders vorliegen (die jeweilige Übersetzung des Application Builders muss installiert sein). Im PL/SQL Kontext kann SINCE mit der Funktion APEX_UTIL.GET_SINCE eingesetzt werden; SINCE kann allerdings nicht bei Aufrufen von TO_DATE oder TO_CHAR genutzt werden.

In einem Bericht wird SINCE, analog zu DD.MM.YYYY oder DD-MON-RR, einfach als Formatmaske für eine Berichtsspalte eingetragen.

SINCE in einem eigenen Bericht nutzen

Auch in APEX-Formularen ist DATE oder TIMESTAMP wichtig; nahezu alle Formulare enthalten in irgendeiner Art und Weise Datumswerte. APEX speichert jedes Page Item im Session State allerdings als VARCHAR2, ganz gleichgültig, ob es als Textfeld, Number Field oder Date Picker dargestellt wird.

Das bedeutet, dass die bis hierher beschriebenen Regeln auch für APEX-Formularelemente gelten. Wird ein Formular geladen, also die Werte der Tabellenzeile in die Formularelemente gelesen, so findet eine Konvertierung nach VARCHAR2 statt. Wenn keine explizite Formatmaske hinterlegt ist, so finden die Einstellungen in den Application Attributes Anwendung; ist dort nichts hinterlegt, so gelten die Defaults der Oracle-Datenbank.

Das sei am Beispiel eines Date Picker Formularelements erläutert. Es sei angenommen, dass beim Date Picker die explizite Formatmaske DD.MM.YYYY hinterlegt ist.

Date Picker Formularelement mit expliziter Formatmaske

• Wenn die Seite geladen wird, wird eine Tabellenzeile gelesen. Die Spalte HIREDATE wird in diesem Fall mit einem TO_CHAR(HIREDATE, 'DD.MM.YYYY') selektiert, so dass das Element P22_HIREDATE bspw. mit dem Inhalt 14.02.1982 gefüllt wird.
• Die gleiche Formatmaske wird an die Javascript Bibliothek für den Date Picker übergeben (sie muss ein wenig umformatiert werden), dieser rendert damit die konkrete Datumsauswahl.
• Hat der Nutzer ein Datum ausgewählt, so setzt die Javascript-Bibliothek das Browser-Element mit dem neuen Datum - wiederum im Format DD.MM.YYYY.
• Beim Page Submit wird das Datum im Format DD.MM.YYYY an Application Express gesendet und auch genau so, als VARCHAR2, im APEX Session State gespeichert. Beim Speichern in die Tabelle generiert Application Express schließlich einen TO_DATE( {Datum}, 'DD.MM.YYYY') Ausdruck, um das Datum wieder in ein DATE zu konvertieren.

Application Express Elemente sind stets VARCHAR2 - und beim Übergang in eine Tabellenspalte oder eine PL/SQL Variable vom Typ DATE oder TIMESTAMP finden Konvertierungen statt. Und das ist völlig unabhängig vom Elementtyp - bei einem Textfeld ist es genauso wie bei einem Date Picker. Während der erste das VARCHAR2-Datum einfach nur darstellt, zeigt letzterer anhand dem Datum und der Formatmaske einen Dialog an.

Dies sollte man bei allen PL/SQL-Prozessen und SQL-Abfragen, die man in seine Anwendungen einbaut, im Hinterkopf haben. Schreibt man einen "Page Submit PL/SQL Prozess", der mit einem Element vom Typ Date Picker arbeitet, so sollte man auf die Formatmaske achten: Ist beim Formularelement eine solche hinterlegt, sollte man diese auch im PL/SQL Code verwenden.

declare
    l_date date;
begin
    l_date := to_date( :P22_HIREDATE, 'DD.MM.YYYY' );
    :
    -- processing here
end;

Natürlich ist das Thema "Datumsverarbeitung" mit diesen Erläuterungen bei weitem nicht abgedeckt; mit einer groben Unterscheidung der Datentypen und den Ausführungen zu Formatmasken wurde gerade die Oberfläche berührt. Ein weiteres, sehr interessantes Thema ist die Datumsarithmetik oder das Arbeiten mit Datumsintervallen, was jedoch einem anderen Community-Tipp vorbehalten bleiben muss.

Zurück zur Community-Seite