SQLite Konfusion bei Datum bzw strftime()

[Doc Cyber]

Guten Tag allerseits,

Im Grunde ist ein es Fragenkomplex, wobei die folgenden Einzelfragen naturgemäß zusammenhängen:
1) Wie bekomme ich ein Datum mittels Java-Code in eine SQLite-DB?
2) Wie bekomme ich es in einer View korrekt angezeigt?
3) Wie bekomme ich es mittels Java-Code wieder aus der DB heraus?

Ich habe mich damit eingehend befasst, aber die Informationen sind eher verwirrend als hilfreich.
Was habe ich gemacht?
Tabelle mit einem Datumsfeld erzeugt und mit Java einen Wert eingetragen:
CREATE TABLE 'DOKUMENT' ('Druckdatum' DATETIME NOT NULL)

PreparedStatement ps;
java.sql.Date druckDatum;
druckDatum = einDatumAlsLongWert;
ps.setDate(druckDatum);

Schaue ich mir die Tabelle an, sehe ich auch <i>einDatumAlsLongWert</i>.
Aber natürlich kann man damit nicht viel anfangen.

In der View möchte ich mir daher diesen Wert in fomatierter Form ansehen:
SELECT strftime('%H:%M:%S', Druckdatum) FROM DOKUMENT.
Die Ausgabe bzw Anzeige in der View ist aber 0 ! Warum? Was ist hier falsch?

Nun ist es in SQLite ja möglich, ein Datum sowohl als DATETIME, TEXT und INTEGER zu hinterlegen, und für alle drei Fälle kann man problemlos z.B. das aktuelle Datum automatisch setzen lassen (DEFAULT CURRENT_DATE). Welches Format ist sinnvoll?

Fragen über Fragen...

Any comments??

Dank vorab.

 

[Harry Kane]

Hallo,
ich habe vor kurzem auch mit SQlite angefangen und bin auch zunächst über Datumswerte gestolpert. Ich zeige am besten mal einen Codeausschnitt aus einem TableModel, mit dem ich einen ResultSet in einer JTable anzeigen lassen kann:

public void refresh(ResultSet rs) throws SQLException {
   ResultSetMetaData rsmd = rs.getMetaData();
   int columnCount = rsmd.getColumnCount();
   columnNames = new String[columnCount];
   for (int column = 1; column <= columnCount; column++) {
     columnNames[column - 1] = rsmd.getColumnName(column);
   }
   data.clear();
   while (rs.next()) {
     Object[] rowData = new Object[columnCount];
     for (int i = 1; i <= columnCount; i++) {
       if (rsmd.getColumnType(i) == Types.DATE) {
         rowData[i - 1] = sdf.format(rs.getDate(i));
       } else {   
         rowData[i - 1] = rs.getObject(i);
       }
     }
     data.add(rowData);
   }
   System.out.println("Column and row count: " + columnCount + " and " + data.size());
   fireTableDataChanged();
}

Nun zu deinen Fragen:
1. hast du ja im Prinzip schon beantwortet. Spalte als DATETIME anlegen, neue Werte können als PreparedStatement.setLong(int, long) hinzugefügt werden.
2. Ich würde die long-Werte erst in der View in einen String umwandeln, und zwar mit einem geeigneten DateFormat. Für obiges Beispiel habe ich ein new SimpleDateFormat("E,dd.MM.yy HH:mm:ss") verwendet.
3. Die Frage musseigentlich vor 2. geklärt werden. Antwort s. dort: als long, den du danach beliebig formatieren kannst.

 

[Thallius]

Es gibt kein DATETIME in SQLite. Es gibt lediglich eine datetime() Funktion die einen Timestamp in das gewünschte Format bringt.

 

[Doc Cyber]

Danke für deinen Beispielcode.
Ich muss mich nochmals ransetzen, damit ich den Knoten aus dem Gehirn bekomme.

 

[Doc Cyber]

Es gibt kein DATETIME in SQLite. Es gibt lediglich eine datetime() Funktion die einen Timestamp in das gewünschte Format bringt.

Danke für deinen Kommentar.

 

[Harry Kane]

Es gibt kein DATETIME in SQLite

Offenbar hat ja zumindest das Create Table Statement des TO mit Verwendung von DATETIME als Spaltentyp funktioniert. Bei mir hat ein ähnliches Statement ebenfalls funktioniert.
Was meinst du also mit dieser komischen Aussage?

 

[Thallius]

Offenbar hat ja zumindest das Create Table Statement des TO mit Verwendung von DATETIME als Spaltentyp funktioniert. Bei mir hat ein ähnliches Statement ebenfalls funktioniert.
Was meinst du also mit dieser komischen Aussage?

Ich weis nicht was an meiner Aussage komisch ist aber sag mir bitte wo in den offiziellen Docs zu Sqlite eine Datetime zu finden ist

https://www.sqlite.org/datatype3.html

 

[Doc Cyber]

Offenbar hat ja zumindest das Create Table Statement des TO mit Verwendung von DATETIME als Spaltentyp funktioniert. Bei mir hat ein ähnliches Statement ebenfalls funktioniert.
Was meinst du also mit dieser komischen Aussage?

 

[Doc Cyber]

Ich weiß, was Thallius meint, und das war mir auch vorher schon halbwegs klar.
Aber sowohl seine Aussagen, als auch die von ihm korrekt zitierte Site sind eher Teil der Ursache meiner Verwirrung. Wenn mir das geholfen hätte, würde ich meine Fragen nicht stellen.
Es gibt eben Aussagen, die völlig korrekt sind, und es gibt solche, die für eine Lösung brauchbar sind.

Was er (Thallius) vermutlich meint: Intern speichert SQLite alle Datumswerte als INTEGER. Die Möglichkeit, Datumswerte in Tabellenfelder abzulegen, die als bei der Tabellendefinition als INTEGER, DATETIME oder TEXT klassifiziert sind, ist lediglich eine Art der Vereinfachung. Offenbar werden alle Werte eben intern immer in INTEGER konvertiert.

 

[Harry Kane]

Danke für deinen Beitrag, Doc.
Mit gings genauso wie dir. Ich suchte auch nach einer Möglichkeit, Datumswerte in einer SQlite db unterzubringen, allerdings mit der zusätzlichen Anforderung, das Datenbank backend gegen eine andere db (embedded oder nicht) austauschen zu können, ohne die ganzen SQL Statements neu schreiben zu müssen. Ich bin auch über die von Thallius zitierte Seite gestolpert und bekam da nicht die Antwort, die ich gesucht habe. Dann habe ich versucht, bei einer neu angelegten Tabelle DATETIME als Spaltenytp anzugegeben, und siehe da, es ging. Die zweite Frage war, wie ich die Daten am besten wieder raus bekomme. Beim Rumprobieren stelle sich dann raus, das 1. der ColumnType java.sql.Types.DATE ist, und 2. der Wert als Date aus dem ResultSet abgefragt werden kann. Case closed.
@Thallius: Sowohl der TO als auch ich haben Create Table statements abgesetzt, bei denen DATETIME als Spaltentyp verwendet wurde, und zwar ohne Fehler. Deswegen war/ist mir der Sinn deines Beitrags nicht klar.
Sollte das der Fehlerfindung dienen? Eher nicht, denn DATETIME Spalten werden ja bei den statements ohne Fehler akzeptiert.
War es ein Ratschlag/eine Warnung, das nicht zu tun, weil ev. fehleranfällig, unperformant oder aus anderen Gründen ein NoGo? Dann kannst du das gerne so schreiben.

 

[Thallius]

Der Punkt ist, dass man wissen sollte, dass SQLite intern nur mit Timestamps arbeitet. Das DateTime ist nur eine Krücke die aber eben nicht dem DateTime von anderen DB entspricht. Somit bist Du mit Deiner Methodik genau darauf reingefallen.
Ein richtiges DateTime enthält auch Zeitzonen Informationen. Wenn Du jetzt mit deinem System z.B. 2015-12-04 9:30:00:000 in Deine SQlite Datenbnak schreibst, dann muss Dir klar sein, dass das in SQLite dann die UTC Zeit ist und damit nicht der wirklich Zeit hier in Deutschland gerade entspricht.

 

[Harry Kane]

Somit bist Du mit Deiner Methodik genau darauf reingefallen.

Ich beschreibe hier mal, wie die Werte in die db kommen und wie sie draus gelesen werden. Vielleicht magst du mir ja erklären, wo genau die Falle liegt. Ich plane übrigens nicht, das Tool woran ich arbeite, in einer anderen Zeitzone zu nutzen.
1. Datumseingabe: Hierzu habe ich mir eine Ableitung von JTextField gebastelt, welches eine Eingabe im Format "E,dd.MM.yy HH:mm:ss" erlaubt. Das JTextField hat ein von PlainDocument abgeleitetes Dokument, welches die Texteingaben mit Hilfe einer Instanz von SimpleDateFormat in Instanzen von java.util.Date verwaltet. Der Wert von date.getTime() wird dann mit PreparedStatement.setLong(int, long) in die db geschrieben.
2. Datumsabfrage: Der long-Wert aus der db wird mit Hilfe einer Instanz von SimpleDateFormat in einen String der Form "E,dd.MM.yy HH:mm:ss" umgewandelt.
Also bisher sieht alles unverdächtig aus. Das Tool ist aber noch sehr neu (gerade erst zusammengenagelt), und vielleicht habe ich was übersehen.

 

[Thallius]

zu 1) Sowas würde ich niemals sagen. Morgen kommt ein internationaler Konzern und will Deine Software vermarkten...

zu 2. und 3.) Wenn du die Daten als long schreibst und liest ist ja alles ok. Ich war jetzt davon ausgegangen, dass Du Deinen String in die DB schreibst und da SQlite diesen eben nicht zeitzonengerecht umrechnen kann, würde dann der falsche Timestamp in der DB stehen.

 

[Doc Cyber]

Vielleicht magst du mir ja erklären, wo genau die Falle liegt.

Ja, das möchte ich gern zusammenfassen.
Die ursprüngliche Grund für meine Fragen ist, dass Java bei
<JAVA> ps.setDate(druckDatum); </JAVA>
für die Variable druckDatum ein LongInt verlangt, um die Zeit in Millisekunden seit 1.1.1970 aufzunehmen. Demnach entspricht ein LongInt = 0 dem 1.1.1970. Zumindest habe ich das so verstanden.

Also hab ich es ausprobiert. Dazu habe ich eine Tabelle erstellt, die das Feld DatumAsInteger beinhaltet...

CREATE TABLE "tbl_DATUM" (
"ID" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL UNIQUE ,
"dummyText" TEXT,
"DatumAsInteger" INTEGER DEFAULT CURRENT_DATE)​

... und habe den Wert 0 in dieses Feld geschrieben.

Dann hab ich eine View gemacht, die mir das Datum anzeigt:

CREATE VIEW "testview" AS SELECT
DatumAsInteger ,
strftime('%Y-%m-%d', DatumAsInteger) AS Datum
FROM tbl_DATUM​

Dummerweise sehe ich in der View aber nicht das Datum (1970.01.01), sondern -471-11-24.

[Zu Beginn dieses Themas hatte ich geschrieben, dass ich 0 als Datum angezeigt bekomme. Das lag aber daran, dass ich versucht hatte, den Formatierungsstring '%Y-%m-%d' auf ein Zeitfeld (nicht Datum) anzuwenden.]

Da bekanntlich das Problem immer vor dem Monitor zu finden ist , muss ich irgendetwas falsch verstanden und/oder falsch gemacht haben. (Übrigens nutze ich für derartige Dinge den Firefor SQLite Manager) .


Habt Dank für eure erleuchtenden Worte...!