SQLite DateTime comparison

Question

I can t seem to get reliable results from the query against a sqlite database using a datetime string as a comparison as so   select      from table 1   where mydate  gt    1 1 2009  and mydate  lt    5 5 2009    how should I handle datetime comparisons to sqlite   update  field mydate is a DateTime datatype

User · Answer

Sqlite can not compare on dates  we need to convert into seconds and cast it as integer    Example  SELECT   FROM Table   WHERE   CAST strftime   s   date field   AS  integer   lt  CAST strftime   s    2015-01-01    AS  integer

User · Answer

Right now i am developing using System Data SQlite NuGet package  version 1 0 109 2   Which using SQLite version 3 24 0   And this works for me   SELECT   FROM tables WHERE datetime  BETWEEN  2018-10-01 00 00 00  AND  2018-10-10 23 59 59     I don t net to use the datetime   function  Perhaps they already updated the SQL query on that SQLite version

User · Answer

I had to store the time with the time-zone information in it  and was able to get queries working with the following format    SELECT   FROM events WHERE datetime date added  BETWEEN        datetime  2015-03-06 20 11 00 -04 00   AND datetime  2015-03-06 20 13 00 -04 00      The time is stored in the database as regular TEXT in the following format   2015-03-06 20 12 15 -04 00

User · Answer

SQLite doesn t have dedicated datetime types  but does have a few datetime functions   Follow the string representation formats  actually only formats 1-10  understood by those functions  storing the value as a string  and then you can use them  plus lexicographical comparison on the strings will match datetime comparison  as long as you don t try to compare dates to times or datetimes to times  which doesn t make a whole lot of sense anyway    Depending on which language you use  you can even get automatic conversion    Which doesn t apply to comparisons in SQL statements like the example  but will make your life easier

User · Answer

The following is working fine for me using SQLite   SELECT        FROM ingresosgastos      WHERE fecharegistro BETWEEN  2010-01-01  AND  2013-01-01

User · Answer

I have a situation where I want data from up to two days ago and up until the end of today  I arrived at the following   WHERE dateTimeRecorded between date  now    start of day   -2 days                               and date  now    start of day     1 day      Ok  technically I also pull in midnight on tomorrow like the original poster   if there was any data  but my data is all historical   The key thing to remember  the initial poster excluded all data after 2009-11-15 00 00 00  So  any data that was recorded at midnight on the 15th was included but any data after midnight on the 15th was not  If their query was    select      from table 1    where mydate between Datetime  2009-11-13 00 00 00                       and Datetime  2009-11-15 23 59 59     Use of the between clause for clarity   It would have been slightly better  It still does not take into account leap seconds in which an hour can actually have more than 60 seconds  but good enough for discussions here

User · Answer

Below are the methods to compare the dates but before that we need to identify the format of date stored in DB  I have dates stored in MM DD YYYY HH MM format so it has to be compared in that format   Below query compares the convert the date into MM DD YYY format and get data from last five days till today  BETWEEN operator will help and you can simply specify start date AND end date   select   from myTable where myColumn BETWEEN strftime   m  d  Y  H  M   datetime  now   localtime     -5 day   AND strftime   m  d  Y  H  M  datetime  now   localtime       Below query will use greater than operator          select   from myTable where myColumn   strftime   m  d  Y  H  M   datetime  now   localtime     -5 day        All the computation I have done is using current time  you can change the format and date as per your need    Hope this will help you  Summved

User · Answer

I had the same issue recently  and I solved it like this   SELECT   FROM table WHERE      strftime   s   date  BETWEEN strftime   s   start date  AND strftime   s   end date

User · Answer

My query I did as follows   SELECT COUNT carSold   FROM cars sales tbl WHERE date BETWEEN  2015-04-01  AND  2015-04-30  AND carType    Hybrid    I got the hint by  ifredy s answer  The all I did is  I wanted this query to be run in iOS  using Objective-C  And it works   Hope someone who does iOS Development  will get use out of this answer too

User · Answer

You could also write up your own user functions to handle dates in the format you choose   SQLite has a fairly simple method for writing your own user functions   For example  I wrote a few to add time durations together

User · Answer

Following worked for me   SELECT   FROM table log WHERE DATE start time   lt    2017-01-09  AND DATE start time   gt    2016-12-21

User · Answer

To solve this problem  I store dates as YYYYMMDD  Thus     where mydate  gt    20090101  and mydate  lt    20050505   It just plain WORKS all the time   You may only need to write a parser to handle how users might enter their dates so you can convert them to YYYYMMDD

[sql] SQLite DateTime comparison

Examples related to sql

Examples related to sqlite