Search code examples
sqlitedatetimef#type-providers

SQLite DateTime handling in SQLProvider

How to use DateTime with SQLite via the SQLProvider type-provider?

SQLite doesn't really have a date and time datatype (see Data types) and stores dates as text. I can pass a date string and query it, and get back a string. So this works, where Date1 in table3 was stored as a string:

query {                                         
    for r in table3 do
        select (r.Date1)                                
        } |> Seq.toList

val it : string list =
  ["2016/06/09 0:00:00"; "2016/06/05 0:00:00"; "2016/06/04 0:00:00";
   "2016/06/12 0:00:00"; "2016/06/10 0:00:00"; "2016/06/06 0:00:00";

It is also possible to store Date1 as a DateTime, and in another table I have it as such. That is even though SQLite doesn't understand DateTime, I can create a column with the DateTime data type, and I can store a DateTime value in it. I can extract this value in C# (or LinqPad) for example. But when I try to access it via the type provider (the same type provider that let me store the DateTime value), it gives the following error:

System.FormatException: String was not recognized as a valid DateTime.

>    at System.DateTimeParse.ParseExactMultiple(String s, String[] formats, DateTimeFormatInfo dtfi, DateTimeStyles style)
   at System.Data.SQLite.SQLiteConvert.ToDateTime(String dateText, SQLiteDateFormats format, DateTimeKind kind, String formatString)
   at System.Data.SQLite.SQLite3.GetDateTime(SQLiteStatement stmt, Int32 index)
   at System.Data.SQLite.SQLite3.GetValue(SQLiteStatement stmt, SQLiteConnectionFlags flags, Int32 index, SQLiteType typ)
   at System.Data.SQLite.SQLiteDataReader.GetValue(Int32 i)
   at <StartupCode$FSharp-Data-SqlProvider>.$SqlRuntime.DataContext.FSharp-Data-Sql-Common-ISqlDataContext-ReadEntities@153.GenerateNext(IEnumerable`1& next)
   at Microsoft.FSharp.Core.CompilerServices.GeneratedSequenceBase`1.MoveNextImpl()
   at System.Collections.Generic.List`1..ctor(IEnumerable`1 collection)
   at Microsoft.FSharp.Collections.SeqModule.ToArray[T](IEnumerable`1 source)
   at FSharp.Data.Sql.Runtime.QueryImplementation.executeQuery(ISqlDataContext dc, ISqlProvider provider, SqlExp sqlExp, List`1 ti)
   at FSharp.Data.Sql.Runtime.QueryImplementation.SqlQueryable`1.System-Collections-Generic-IEnumerable`1-GetEnumerator()
   at Microsoft.FSharp.Collections.SeqModule.ToList[T](IEnumerable`1 source)
   at <StartupCode$FSI_0075>.$FSI_0075.main@()

The difference between the working (type provider) query in table3 and the one with the error in table2 is the data type of the column:

enter image description here

LinqPad correctly sees it as a DateTime in Table2 and this query works there:

var dt2 = new System.DateTime(2016,8,30,0,0,0,DateTimeKind.Local);
Table2
    .Where(r => r.Date1 == dt2).Dump();
Solution

  • DateTime type is working as intended with the following versions with SQLProvider 1.0.31 and SQLite 1.0.102:

    #if INTERACTIVE
#I @"..\packages\SQLProvider.1.0.31\lib"
#r "FSharp.Data.SqlProvider.dll" 
#I @"..\packages\System.Data.SQLite.Core.1.0.102.0\lib\net46"
#r "System.Data.SQLite.dll"
#I @"..\packages\System.Data.SQLite.Linq.1.0.102.0\lib\net46"
#r "System.Data.SQLite.Linq.dll"
#endif

open System
open FSharp.Data.Sql
//open System.Data.SQLite
//open System.Data.SQLite.Linq

[<Literal>]
let connectionString = "Data Source="+ @"C:\tmp\databaseFile.db3"
[<Literal>]
let resolutionPath = __SOURCE_DIRECTORY__ + @"..\..\packages\System.Data.SQLite.Core.1.0.102.0\lib\net46"

type sql = SqlDataProvider<
                Common.DatabaseProviderTypes.SQLITE, 
                connectionString, 
                ResolutionPath = resolutionPath, 
                CaseSensitivityChange = Common.CaseSensitivityChange.ORIGINAL>

let ctx = sql.GetDataContext() 

let table2 = ctx.Main.Table2   //DateTime
let table3 = ctx.Main.Table3   //Text