blocks|key|785418|text|SQLite没有专用的datetime+types，但有一个few+datetime+functions。遵循那些函数理解的字符串表示格式(实际上只有格式1-10)+(将值存储为字符串)，然后就可以使用它们，加上字符串上的字典序比较将匹配datetime比较(只要你不试图比较日期与时间或日期时间与时间，这本来就没有太大意义)。|type|unstyled|depth|inlineStyleRanges|entityRanges|offset|length|data|785419|根据您使用的语言，您甚至可以获得automatic+conversion。(这不适用于SQL语句中的比较，但会让您的工作更轻松。)|785420|entityMap|0|LINK|mutability|MUTABLE|url|http://sqlite.org/datatype3.html|1|http://sqlite.org/lang_datefunc.html|2|http://docs.python.org/library/sqlite3.html#default-adapters-and-converters^0|K|5|0|U|M|1|0|G|K|2|0^^$0|@$1|2|3|4|5|6|7|R|8|@]|9|@$A|S|B|T|1|U]|$A|V|B|W|1|X]]|C|$]]|$1|D|3|E|5|6|7|Y|8|@]|9|@$A|Z|B|10|1|11]]|C|$]]|$1|F|3|-4|5|6|7|12|8|@]|9|@]|C|$]]]|G|$H|$5|I|J|K|C|$L|M]]|N|$5|I|J|K|C|$L|O]]|P|$5|I|J|K|C|$L|Q]]]]

SQLite doesn't have dedicated datetime <a href="http://sqlite.org/datatype3.html" rel="noreferrer">types</a>, but does have a <a href="http://sqlite.org/lang_datefunc.html" rel="noreferrer">few datetime functions</a>. 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 <a href="http://docs.python.org/library/sqlite3.html#default-adapters-and-converters" rel="noreferrer">automatic conversion</a>. (Which doesn't apply to comparisons in SQL statements like the example, but will make your life easier.)

blocks|key|658978|text|为了解决这个问题，我将日期存储为YYYYMMDD。因此，where+mydate+>=+'20090101'+and+mydate+<=+'20050505'|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|658979|它一直都很好用。您可能只需要编写一个解析器来处理用户输入日期的方式，这样您就可以将它们转换为YYYYMMDD。|658980|entityMap^0|G|8|S|1F|0|1A|8|0^^$0|@$1|2|3|4|5|6|7|J|8|@$9|K|A|L|B|C]|$9|M|A|N|B|C]]|D|@]|E|$]]|$1|F|3|G|5|6|7|O|8|@$9|P|A|Q|B|C]]|D|@]|E|$]]|$1|H|3|-4|5|6|7|R|8|@]|D|@]|E|$]]]|I|$]]

To solve this problem, I store dates as <code>YYYYMMDD</code>. Thus,
 <code>where mydate &gt;= '20090101' and mydate &lt;= '20050505'</code>

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 <code>YYYYMMDD</code>.

blocks|key|1959447|text|我最近遇到了同样的问题，我是这样解决的：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1959448|SELECT+*+FROM+table+WHERE+
++++strftime('%25s',+date)+BETWEEN+strftime('%25s',+start_date)+AND+strftime('%25s',+end_date)|code-block|syntax|javascript|1959449|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

I had the same issue recently, and I solved it like this:

<pre><code>SELECT * FROM table WHERE 
 strftime('%s', date) BETWEEN strftime('%s', start_date) AND strftime('%s', end_date)
</code></pre>

blocks|key|2194124|text|下面的代码在我使用SQLite时运行得很好：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|2194125|SELECT+*+
++++FROM+ingresosgastos+
++++WHERE+fecharegistro+BETWEEN+"2010-01-01"+AND+"2013-01-01"|code-block|syntax|javascript|2194126|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

The following is working fine for me using SQLite:

<pre><code>SELECT * 
 FROM ingresosgastos 
 WHERE fecharegistro BETWEEN "2010-01-01" AND "2013-01-01"
</code></pre>

blocks|key|785651|text|下面的内容对我很有效。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|785652|SELECT+*
FROM+table_log
WHERE+DATE(start_time)+<=+'2017-01-09'+AND+DATE(start_time)+>=+'2016-12-21'|code-block|syntax|javascript|785653|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

Following worked for me.

<pre><code>SELECT *
FROM table_log
WHERE DATE(start_time) &lt;= '2017-01-09' AND DATE(start_time) &gt;= '2016-12-21'
</code></pre>

blocks|key|2194204|text|Sqlite无法在日期上进行比较。我们需要转换为秒，并将其转换为整数。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|2194205|示例|2194206|SELECT+*+FROM+Table++
WHERE++
CAST(strftime('%25s',+date_field)++AS++integer)+<=CAST(strftime('%25s',+'2015-01-01')++AS++integer)+;|code-block|syntax|javascript|2194207|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|K|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|L|8|@]|9|@]|A|$]]|$1|D|3|E|5|F|7|M|8|@]|9|@]|A|$G|H]]|$1|I|3|-4|5|6|7|N|8|@]|9|@]|A|$]]]|J|$]]

Sqlite can not compare on dates. we need to convert into seconds and cast it as integer. 

Example

<pre><code>SELECT * FROM Table 
WHERE 
CAST(strftime('%s', date_field) AS integer) &lt;=CAST(strftime('%s', '2015-01-01') AS integer) ;
</code></pre>

blocks|key|1959559|text|我有一种情况，我需要从两天前到今天结束的数据。我达到了以下目标。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1959560|WHERE+dateTimeRecorded+between+date('now',+'start+of+day','-2+days')+
+++++++++++++++++++++++++++and+date('now',+'start+of+day',+'%2B1+day')+|code-block|syntax|javascript|1959561|好的，从技术上讲，如果有任何数据的话，我也会像最初的海报一样在明天午夜到达，但我的数据都是历史数据。|1959562|要记住的关键一点是，最初的海报排除了2009-11-15+00:00:00之后的所有数据。因此，在15日午夜记录的任何数据都包括，但在15日午夜之后记录的任何数据都不是。如果他们的查询是，|offset|length|style|BOLD|1959563|select+*+
++from+table_1+
++where+mydate+between+Datetime('2009-11-13+00:00:00')+
+++++++++++++++++++and+Datetime('2009-11-15+23:59:59')|1959564|为了清楚起见，使用了between子句。|1959565|这可能会稍微好一点。它仍然没有考虑到闰秒，其中一个小时实际上可以有超过60秒，但足以在这里讨论:)|1959566|entityMap^0|0|0|0|29|D|0|0|0|K|0|0|1D|0^^$0|@$1|2|3|4|5|6|7|W|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|X|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|Y|8|@]|9|@]|A|$]]|$1|I|3|J|5|6|7|Z|8|@$K|10|L|11|M|N]]|9|@]|A|$]]|$1|O|3|P|5|D|7|12|8|@]|9|@]|A|$E|F]]|$1|Q|3|R|5|6|7|13|8|@$K|14|L|15|M|N]]|9|@]|A|$]]|$1|S|3|T|5|6|7|16|8|@$K|17|L|18|M|N]]|9|@]|A|$]]|$1|U|3|-4|5|6|7|19|8|@]|9|@]|A|$]]]|V|$]]

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.

<pre><code>WHERE dateTimeRecorded between date('now', 'start of day','-2 days') 
 and date('now', 'start of day', '+1 day') 
</code></pre>

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, 

<pre><code>select * 
 from table_1 
 where mydate between Datetime('2009-11-13 00:00:00') 
 and Datetime('2009-11-15 23:59:59')
</code></pre>

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 :)

blocks|key|2194149|text|我必须存储包含时区信息的时间，并且能够使用以下格式进行查询：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|2194150|"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')"|code-block|syntax|javascript|2194151|时间以常规文本的形式存储在数据库中，格式如下：|2194152|2015-03-06+20:12:15+-04:00|2194153|entityMap^0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|M|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|N|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|O|8|@]|9|@]|A|$]]|$1|I|3|J|5|D|7|P|8|@]|9|@]|A|$E|F]]|$1|K|3|-4|5|6|7|Q|8|@]|9|@]|A|$]]]|L|$]]

I had to store the time with the time-zone information in it, and was able to get queries working with the following format:

<pre><code>"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')"
</code></pre>

The time is stored in the database as regular TEXT in the following format:

<pre><code>2015-03-06 20:12:15 -04:00
</code></pre>

blocks|key|659125|text|现在我正在使用System.Data.SQlite+NuGet包(版本1.0.109.2)进行开发。它使用SQLite版本3.24.0。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|659126|这对我很有效。|659127|SELECT+*+FROM+tables+WHERE+datetime+
BETWEEN+'2018-10-01+00:00:00'+AND+'2018-10-10+23:59:59';|code-block|syntax|javascript|659128|我不需要使用datetime()函数。也许他们已经在那个SQLite版本上更新了SQL查询。|659129|entityMap^0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|M|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|N|8|@]|9|@]|A|$]]|$1|D|3|E|5|F|7|O|8|@]|9|@]|A|$G|H]]|$1|I|3|J|5|6|7|P|8|@]|9|@]|A|$]]|$1|K|3|-4|5|6|7|Q|8|@]|9|@]|A|$]]]|L|$]]

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.
<pre><code>SELECT * FROM tables WHERE datetime 
BETWEEN '2018-10-01 00:00:00' AND '2018-10-10 23:59:59';
</code></pre>
I don't need to use the datetime() function. Perhaps they already updated the SQL query on that SQLite version.

blocks|key|2194082|text|您还可以编写your+own+user+functions来处理您选择的格式的日期。SQLite有一个相当简单的方法来编写您自己的用户函数。例如，我写了几个将持续时间加在一起的代码。|type|unstyled|depth|inlineStyleRanges|entityRanges|offset|length|data|2194083|entityMap|0|LINK|mutability|MUTABLE|url|http://souptonuts.sourceforge.net/readme_sqlite_tutorial.html^0|6|N|0|0^^$0|@$1|2|3|4|5|6|7|L|8|@]|9|@$A|M|B|N|1|O]]|C|$]]|$1|D|3|-4|5|6|7|P|8|@]|9|@]|C|$]]]|E|$F|$5|G|H|I|C|$J|K]]]]

You could also write up <a href="http://souptonuts.sourceforge.net/readme_sqlite_tutorial.html" rel="nofollow noreferrer">your own user functions</a> 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.

blocks|key|1961566|text|我的查询如下：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1961567|SELECT+COUNT(carSold)+
FROM+cars_sales_tbl
WHERE+date
BETWEEN+'2015-04-01'+AND+'2015-04-30'
AND+carType+=+"Hybrid"|code-block|syntax|javascript|1961568|我从@ifredy的回答中得到了提示。我所做的就是，我希望这个查询使用Objective-C在iOS中运行。而且它起作用了！|1961569|希望做iOS开发的人也能从这个答案中得到用处！|1961570|entityMap^0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|M|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|N|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|O|8|@]|9|@]|A|$]]|$1|I|3|J|5|6|7|P|8|@]|9|@]|A|$]]|$1|K|3|-4|5|6|7|Q|8|@]|9|@]|A|$]]]|L|$]]

My query I did as follows:

<pre><code>SELECT COUNT(carSold) 
FROM cars_sales_tbl
WHERE date
BETWEEN '2015-04-01' AND '2015-04-30'
AND carType = "Hybrid"
</code></pre>

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!

I can't seem to get reliable results from the query against a sqlite database using a datetime string as a comparison as so:

<pre><code>select * 
 from table_1 
 where mydate &gt;= '1/1/2009' and mydate &lt;= '5/5/2009'
</code></pre>

how should I handle datetime comparisons to sqlite?

update:
field mydate is a DateTime datatype

SQLite DateTime comparison

翻译质量差，导致语言生硬或混乱。

没有提供实际的解决方法或示例。

解答不清晰，无法理解或解决问题。

页面排版不美观，阅读体验差。

文章

问答

视频

学习中心

腾讯云实验室

直播

竞赛

腾讯云代码分析专区

腾讯iOA零信任安全管理系统专区

腾讯云架构师技术同盟交流圈

腾讯云数据库专区

腾讯云顾问专区

腾讯云原生专区

腾讯混元专区

腾讯云TCE专区

腾讯云Lighthouse专区

腾讯云HAI专区

腾讯云Edgeone专区

腾讯云存储专区

腾讯云智能专区

腾讯轻联专区 

腾讯云开发专区

TAPD专区

腾讯轻量云游戏服专区

腾讯云最具价值专家

腾讯云架构师技术同盟

腾讯云创作之星

腾讯云开发者先锋

腾讯云代码助手

云原生构建

TAPD 敏捷项目管理

Cloud Studio

SDK中心

API中心

命令行工具

涵盖代码开发、场景应用、自动测试全流程，助你从零构建专属AI助手

一站式MCP教程库，解锁AI应用新玩法

我似乎无法从使用日期时间字符串作为比较的sqlite数据库的查询中获得可靠的结果，因此：select * from table_1 where mydate >= '1/1/2009' and mydate <= '5/5/2009'我应该如何处理与sqlite的日期时间比较？更新:字段mydate是一个DateTime数据类型

问SQLite DateTime比较
EN

回答 11

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问SQLite DateTime比较EN

回答 11

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问SQLite DateTime比较
EN