SQLite DateTime comparison

Ask Time：2009-12-30T01:12:56 Author：Brad

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 >= '1/1/2009' and mydate <= '5/5/2009'

how should I handle datetime comparisons to sqlite?

update: field mydate is a DateTime datatype

Author:Brad，eproduced under the CC 4.0 BY-SA copyright license with a link to the original source and this disclaimer.
Link to original article：https://stackoverflow.com/questions/1975737/sqlite-datetime-comparison

Aruman :

Right now i am developing using System.Data.SQlite NuGet package (version 1.0.109.2). Which using SQLite version 3.24.0.\nAnd this works for me.\nSELECT * FROM tables WHERE datetime \nBETWEEN '2018-10-01 00:00:00' AND '2018-10-10 23:59:59';\n\nI don't need to use the datetime() function. Perhaps they already updated the SQL query on that SQLite version.",

2018-10-27T22:55:39

Summved Jain :

Below are the methods to compare the dates but before that we need to identify the format of date stored in DB\n\nI have dates stored in MM/DD/YYYY HH:MM format so it has to be compared in that format\n\n\nBelow 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.\n\nselect * 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')); \n\nBelow query will use greater than operator (>). \n\n select * from myTable where myColumn > strftime('%m/%d/%Y %H:%M', datetime('now','localtime'), '-5 day'); \n\n\nAll the computation I have done is using current time, you can change the format and date as per your need. \n\nHope this will help you\n\nSummved",

2017-05-10T12:20:34

Mark Smith :

To solve this problem, I store dates as YYYYMMDD. Thus,\n where mydate >= '20090101' and mydate <= '20050505'\n\nIt 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.",

2009-12-29T18:17:19

J. Polfer :

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.",

2009-12-29T18:59:14

Roger Pate :

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).\n\nDepending 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.)",

2009-12-29T17:33:01

Randika Vishman :

My query I did as follows:\n\nSELECT COUNT(carSold) \nFROM cars_sales_tbl\nWHERE date\nBETWEEN '2015-04-01' AND '2015-04-30'\nAND carType = \"Hybrid\"\n\n\nI 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!\n\nHope someone who does iOS Development, will get use out of this answer too!",

2015-04-28T14:59:32

Simon :

I had the same issue recently, and I solved it like this:\n\nSELECT * FROM table WHERE \n strftime('%s', date) BETWEEN strftime('%s', start_date) AND strftime('%s', end_date)\n",

2011-09-06T08:08:56

ifredy :

The following is working fine for me using SQLite:\n\nSELECT * \n FROM ingresosgastos \n WHERE fecharegistro BETWEEN \"2010-01-01\" AND \"2013-01-01\"\n",

2012-08-05T23:46:50

Jose Jithin Stanly :

Following worked for me.\n\nSELECT *\nFROM table_log\nWHERE DATE(start_time) <= '2017-01-09' AND DATE(start_time) >= '2016-12-21'\n",

2017-01-12T07:06:45

Hardeep Singh :

Sqlite can not compare on dates. we need to convert into seconds and cast it as integer. \n\nExample\n\nSELECT * FROM Table \nWHERE \nCAST(strftime('%s', date_field) AS integer) <=CAST(strftime('%s', '2015-01-01') AS integer) ;\n",

2015-09-08T12:12:29

Lyall Pearce :

I have a situation where I want data from up to two days ago and up until the end of today.\nI arrived at the following.\n\nWHERE dateTimeRecorded between date('now', 'start of day','-2 days') \n and date('now', 'start of day', '+1 day') \n\n\nOk, technically I also pull in midnight on tomorrow like the original poster, if there was any data, but my data is all historical.\n\nThe 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.\nIf their query was, \n\nselect * \n from table_1 \n where mydate between Datetime('2009-11-13 00:00:00') \n and Datetime('2009-11-15 23:59:59')\n\n\nUse of the between clause for clarity.\n\nIt 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 :)",

2017-11-23T07:40:30

Eternal21 :

I had to store the time with the time-zone information in it, and was able to get queries working with the following format:\n\n\"SELECT * FROM events WHERE datetime(date_added) BETWEEN \n datetime('2015-03-06 20:11:00 -04:00') AND datetime('2015-03-06 20:13:00 -04:00')\"\n\n\nThe time is stored in the database as regular TEXT in the following format:\n\n2015-03-06 20:12:15 -04:00\n",

2015-03-12T13:26:22

SQLite DateTime comparison