This is a migrated thread and some comments may be shown as answers.

Sqlite and strftime question.

3 Answers 56 Views
Data Access Free Edition
This is a migrated thread and some comments may be shown as answers.
This question is locked. New answers and comments are not allowed.
Jose Mejia
Top achievements
Rank 1
Jose Mejia asked on 09 Oct 2015, 01:13 PM

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.

3 Answers, 1 is accepted

Sort by
0
Accepted
Viktor Zhivkov
Telerik team
answered on 14 Oct 2015, 01:24 PM
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
 
Check out the latest announcement about Telerik Data Access vNext as a powerful framework able to solve core development problems.
0
Jose Mejia
Top achievements
Rank 1
answered on 15 Oct 2015, 11:08 AM

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.

 

0
Accepted
Viktor Zhivkov
Telerik team
answered on 19 Oct 2015, 03:53 PM
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
 
Check out the latest announcement about Telerik Data Access vNext as a powerful framework able to solve core development problems.
Tags
Data Access Free Edition
Asked by
Jose Mejia
Top achievements
Rank 1
Answers by
Viktor Zhivkov
Telerik team
Jose Mejia
Top achievements
Rank 1
Share this question
or