SQLite-Abfrage mit Datumsfeld

verwendetes Datenbanksystem: SQLite

Hallo an Alle,

habe eine Frage bei der ich nicht weiter komme. Ich habe eine Tabelle in SQLite mit zwei Datumsfeldern mit dem Typ DATETIME.

In dem einen Steht der Wert: 09.07.2017 00:00:00 im anderen 14.12.2017 00:00:00

Wenn ich nun eine Abfrage erstellt die wie folge aussieht passiert gar nichts.

SELECT * FROM Fahrzeuge WHERE JDT_StartDate < '03.11.2017 00:00:00' AND JDT_EndDate > '03.11.2017 00:00:00';

Habe schon mit allem möglichen herumprobiert (2017-11-03 00:00:00 oder nur 2017-11-03).

Bekomme es einfach nicht hin.

Wo liegt mein Fehler? Kann mit jemand evtl. die richtige Abfrage nennen!?

Vielen Dank schon jetzt im Voraus und Gruß an Alle....

"Passiert gar nichts" ist keine Fehlermeldung.

Aber das Format des Zeitstempels scheint einfach falsch zu sein.
Das eine ist ja, wie Deine UI den Zeitwert anzeigt, und das andere wie der Zeitstempel für einen Query aussehen muss

Und letzters ist yyyy-MM-dd HH:mm:ss
Ein ordentlicher SQL Parameter müsste das aber von alleine hin bekommen.

Edit: so stehts auch in der SQLite Dokumentation: http://www.sqlite.org/lang_datefunc.html

Warum nicht gleich auf [Artikelserie] SQL: Parameter von Befehlen verweisen?

Für Sqilte gibt es nur ein halbes Duzend Datentypen und DATETIME gehört
definitiv nicht mit dazu. https://sqlite.org/datatype3.html

Wenn Du die Werte 09.07.2017 00:00:00 und 14.12.2017 00:00:00
im Datensatz stehen hast sind diese vom Typ: TEXT

Insbesondere dann, wenn die Tabelle mit Create Table Fahrzeuge JDT_StartDate DATETIME, .... erstellt wurde.
Alle Sqlite unbekannte Typen werden auf TEXT umgebogen.

Deswegen funktioniert die Abfrage nicht - auch nicht mit Parametern.

Was Du brauchst sind gültige TimeStrings https://sqlite.org/lang_datefunc.html
und diese TimeStrings kommen in Felder des Typ: TEXT

INSERT INTO Fahrzeuge (JDT_StartDate, JDT_EndDate, JDT) VALUES('2017-07-09 00:00:00', '2017-12-14 00:00:00', 'A-FD 2017')

SELECT * FROM Fahrzeuge WHERE JDT_StartDate < '2017-11-03 00:00:00' AND JDT_EndDate > '2017-11-03 00:00:00';

So funktioniert das ( ungeprüft ) und weiter wie in anderen Beiträgen hier im Forum zu lesen - Datum-Zeit-Angaben als UTC speichern und die Zeitzone dazu nicht vergessen.

Sqlite kann zwar die Zeitzone mit speichern - weil der Datum-Zeit-Wert eine TEXT ist 😃

2013-10-07 04:23:19.120-04:00

Andere Datenbansysteme können das aber genau wieder nicht weil die
spezielle Typen für Datum-Zeit-Werte haben. Aus gründen der Kompatibilität
also besser ein Zeitzonen-Spalte pro Datum-Zeit-Wert erstellen.

JavaScript-Programmierer würden sich an einer solchen gültigen Anweisung
zur Tabellen Erstellung nicht stören

CREATE TABLE tableName (f1 MEINEOMA ,f2 DEINOPA)

vola - funktioniert. In die OMA/OPA - Datenbank Typen passen auch Datum-Zeit-Werte oder Fließkommazahlen rein ... 😃

CREATE TABLE tableName (f1 DATETIME ,f2 FUCKSQLITE)

INSERT INTO tableName (f1, f2) VALUES('Lustig ist das Zigeunerleben...', 'Zeitangaben sind doof')

Deine Ausführungen sind fast richtig.

Ja, SQLite hat eigentlich keine DateTime spalte, aber der DataProvider händelt das meiste dennoch korrekt.

Habe mal ein kleines Bespiel ohne irgendwelche ORMapper gemacht, und erstaunlicher weise funktioniert das.

class Program
    {
        static void Main(string[] args)
        {
            var dataPath = Path.Combine(Environment.GetFolderPath(Environment.SpecialFolder.CommonApplicationData), "SQLiteTest");
            Directory.CreateDirectory(dataPath);
            var csb = new SQLiteConnectionStringBuilder();
            csb.DataSource = Path.Combine(dataPath, "TestDb.sdb3");

            if (File.Exists(csb.DataSource))
                File.Delete(csb.DataSource);


            Console.WriteLine("Creating DB");
            SQLiteConnection.CreateFile(csb.DataSource);

            using (SQLiteConnection con = new SQLiteConnection(csb.ToString()))
            {
                con.Open();

                CreateTable(con);

                Console.WriteLine("Inserting 5 Datarows");
                InsertDate(con, "test1", new DateTime(1900, 1, 1, 1, 0, 0));
                InsertDate(con, "test2", new DateTime(1900, 1, 1, 2, 0, 0));
                InsertDate(con, "test3", new DateTime(1900, 1, 1, 2, 1, 0));
                InsertDate(con, "test4", new DateTime(1900, 1, 1, 2, 1, 1));
                InsertDate(con, "test5", new DateTime(1900, 1, 1, 2, 1, 2));

                GetDatesBetween(con, new DateTime(1900, 1, 1, 1, 0, 0), new DateTime(1900, 1, 2, 1, 0, 0));

                GetDatesBetween(con, new DateTime(1900, 1, 1, 2, 1, 0), new DateTime(1900, 1, 1, 2, 1, 1));
            }
            Console.ReadLine();
        }

        private static void CreateTable(SQLiteConnection con)
        {
            Console.WriteLine("Creating Test Table");
            using (var cmd = con.CreateCommand())
            {
                cmd.CommandText = "CREATE TABLE Test( [ID] INTEGER PRIMARY KEY AUTOINCREMENT, [Name] Text, [Date] DateTime)";
                cmd.ExecuteNonQuery();
            }
        }

        private static void GetDatesBetween(SQLiteConnection con, DateTime startDate, DateTime endDate)
        {
            using (var cmd = con.CreateCommand())
            {
                cmd.CommandText = "Select * from Test where ([Date] >= @date1) AND ([Date] <= @date2)";
                cmd.Parameters.AddWithValue("@date1", startDate);
                cmd.Parameters.AddWithValue("@date2", endDate);

                Console.WriteLine($"Reading Rows between {startDate}<->{endDate}");
                using (var reader = cmd.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        var id = reader.GetInt32(0);
                        var name = reader.GetString(1);
                        var dt = reader.GetDateTime(2);
                        Console.WriteLine($"ID:{id} Name:{name} DateTime:{dt}");
                    }
                }
            }
        }

        private static void InsertDate(SQLiteConnection con, string name, DateTime date)
        {
            using (var cmd = con.CreateCommand())
            {
                cmd.CommandText = "INSERT INTO [Test] ([Name], [Date]) Values(@Name,@Date)";
                cmd.Parameters.AddWithValue("@Name", name);
                cmd.Parameters.AddWithValue("@Date", date);
                cmd.ExecuteNonQuery();
            }
        }

natürlich kann DataProvider damit umgehen - aber nur weil Du
anders als Thron die Werte als C# DateTime-Typ angibst und der
Provider daraus einen für Sqlite validen Datetime-STRING baut und
in die Tabelle einträgt. Das was Du in der Demotabelle stehen hast
sieht nicht so aus: 09.07.2017 00:00:00 sondern so: 1900-01-01 02:01:01.

Thron hat aber in seiner Tabelle stehen:
09.07.2017 00:00:00 und 14.12.2017 00:00:00

und nicht wie von Abt missverständlich angenommen wurde, es würde sich dabei um die angezeigten UI-Werte handeln - bzw. es sind die UI-Werte welche
1:1 in die Tabelle geschrieben wurden.

Bei den Formaten die Thron in der Tabelle hat hustet der DataProvider,
spätestens wenn er mit 0000-01-01 00:00:00 konfrontiert wird, was ein
valider Sqlite-Datum-Zeit-Wert ist aber kein Wert der in den c# DateTime Typ passt.

Selbst wenn Du bei Erstellung auf die Angabe DateTime verzichtest oder austauschst
cmd.CommandText = "CREATE TABLE Test( [ID] INTEGER PRIMARY KEY AUTOINCREMENT, [Name] Text, [Date] DateTime)";

funktioniert der Aufruf von
var dt = reader.GetDateTime(2);
korrekt, da erst hier der gewünschte Zieltyp implizit angefordert wird.

sqlite3.cs

 
    internal override void [B]Bind_Text[/B](SQLiteStatement stmt, int index, string value)
    {
      byte[] b = ToUTF8(value);
      int n = UnsafeNativeMethods.[B]sqlite3_bind_text[/B](stmt._sqlite_stmt, index, b, b.Length - 1, (IntPtr)(-1));
      if (n > 0) throw new SQLiteException(n, SQLiteLastError());
    }

    internal override void [B]Bind_DateTime[/B](SQLiteStatement stmt, int index, DateTime dt)
    {
      byte[] b = ToUTF8(dt);
      int n = UnsafeNativeMethods.[B]sqlite3_bind_text[/B](stmt._sqlite_stmt, index, b, b.Length - 1, (IntPtr)(-1));
      if (n > 0) throw new SQLiteException(n, SQLiteLastError());
    }

    internal override DateTime GetDateTime(SQLiteStatement stmt, int index)
    {
#if !SQLITE_STANDARD
      int len;
      return ToDateTime(UnsafeNativeMethods.sqlite3_column_text_interop(stmt._sqlite_stmt, index, out len), len);
#else
      return ToDateTime(UnsafeNativeMethods.sqlite3_column_text(stmt._sqlite_stmt, index), -1);
#endif
    }

und nicht wie von Abt missverständlich angenommen wurde, es würde sich dabei um die angezeigten UI-Werte handeln - bzw. es sind die UI-Werte welche
1:1 in die Tabelle geschrieben wurden.

Ja, mis ist bekannt, dass SQlite kein DateTime Typ hat; aber das sollte trotzdem nicht darin enden, dass man mit String Frickelei den Query baut.
Das DateTime Format ist so, wie ich obe gezeigt habe - steht auch so in der Doku.

Siehe auch Datum in SQLite Datentyp DATE bringen

ja, die Frickelei war der Initialfehler - danach hilft nur noch drop table 😃