Sqlite and strftime question.

[Jose Mejia]

Hi.

I have to query my table for datetime interval. Suppose I have following 4 rows:

id    typeId    start    end
1    11    18.09.2014    18.09.2014 23:45:00
2    11    19.09.2014    19.09.2014 23:45:00
3    12    18.09.2014    18.09.2014 23:45:00
4    12    19.09.2014    19.09.2014 23:45:00

Linq query is following:

model.Entities.Where(t => t.StartTime >= start && t.EndTime <= end);

which becomes something like that:

...

where   a.[StartTime] >= strftime('%Y-%m-%d %H:%M:%f', @p2)
        AND
        a.[EndTime] <= strftime('%Y-%m-%d %H:%M:%f', @p3)

p2 = '2014-09-18' and p3 =2014-09-19 23:45:00.000

As a result I get 2 rows instead of 4 rows. I've invastigated problem and saw that if format string had %S instead of %f  than everything works great. I don't know wheather it is  a bug or expected behaviour, but I would like to get correct results.

I guess that System.Data.Sqlite provider is responsible for generated sql code, but  reading source code of this provider ( SqlGenerator.cs and SQLite3.cs) I didn't found  anything looking strange to me.

The question is: can I influence somehow on generated sql code, can I provide a hint to use​ '%Y-%m-%d %H:%M:%S' format string instead of ​ '%Y-%m-%d %H:%M:%f' ?

I understand that it is possibly not OA problem, but I'm working with OA directly so maybe you will give some hints or direction to look for solution.

Sqlite provider version is 1.0.98.0, OA version is 2015.1.225.1

 

Thanks in advance.

[Viktor Zhivkov]

Hi Jose,

There is no way to influence the date time format string that is generated for your LINQ query.
There is a work around that should give you the expected results while maintaining almost the same LINQ query:

// enable .SQL<T>() extension method
using Telerik.OpenAccess;

// should match your strftime format
string format = "yyyy-MM-dd HH:mm:ss";
var query = model.Entities
    .Where(t => "{0} >= strftime('%Y-%m-%d %H:%M:%S',{1})".SQL<bool>(t.StartTime, start.ToString(format)) &&
           "{0} <= strftime('%Y-%m-%d %H:%M:%S',{1})".SQL<bool>(t.EndTime, end.ToString(format)));

Please note that there is huge difference depending on how your datetime values are stored in the database. SQLite stores them as strings so these three values "2015-10-14 12:00:00", "2015-10-14 12:00:00.000" and "2015-10-14 12:00:00.0000000" are considered different and sorted according their lexical representation rather the numeric (datetime) one. Depending on the format that your existing datetime values are stored you may want to manipulate the value of format string variable and the one in the SQL expression.

Regards,
Viktor Zhivkov
Telerik

 

[Jose Mejia]

Thank you very much, Viktor! 

It works!

1) A little correction: when I substitute directly your code it didn't worked, the  correct version is following:

"strftime('%Y-%m-%d %H:%M:%S',{0})<= strftime('%Y-%m-%d %H:%M:%S',{1})"              .SQL<bool>(t.StartTime, start.ToString(SQLITEDATETIMEFORMAT))

so strftime should be for both operands

2)Wouldn't you be so kind to explain who is reponsoble for sql code generation: OA or System.Data.Sqlite provider (or it's linq library)? I suppose that that  System.Data.Sqlite is responsible and  so threre is a way to change in code format string and use this version. of provider.

 

[Viktor Zhivkov]

Hi Jose,

I am glad that you were able to integrate the suggested work around in your code base. You may find the .SQL() extension method very handy in cases when special handling is required.

As for your second questions - the translation of your LINQ query to SQL is done inside Telerik Data Access runtime and SQLite libraries are used only to facilitate database hosting and communication. We are feeding the SQL statements to the engine and SQLite does not care that you have specified it in LINQ (or C#). At the present moment, I am afraid that there is no easy way to change the format of the date time conversion.

If you have any additional questions, do not hesitate to contact us again.

Regards,
Viktor Zhivkov
Telerik