Sqlite and strftime question.

4 posts, 2 answers
  1. Jose Mejia
    Jose  Mejia avatar
    107 posts
    Member since:
    May 2009

    Posted 09 Oct 2015 Link to this post

    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.

  2. Answer
    Viktor Zhivkov
    Admin
    Viktor Zhivkov avatar
    291 posts

    Posted 14 Oct 2015 Link to this post

    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.
  3. DevCraft banner
  4. Jose Mejia
    Jose  Mejia avatar
    107 posts
    Member since:
    May 2009

    Posted 15 Oct 2015 in reply to Viktor Zhivkov Link to this post

    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.

     

  5. Answer
    Viktor Zhivkov
    Admin
    Viktor Zhivkov avatar
    291 posts

    Posted 19 Oct 2015 Link to this post

    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.
Back to Top