SQL SQLite 日期时间比较
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1975737/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SQLite DateTime comparison
提问by 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:
我似乎无法从使用日期时间字符串作为比较的对 sqlite 数据库的查询中获得可靠的结果：
select *
from table_1
where mydate >= '1/1/2009' and mydate <= '5/5/2009'
how should I handle datetime comparisons to sqlite?
我应该如何处理与 sqlite 的日期时间比较？
update: field mydate is a DateTime datatype
更新：字段 mydate 是 DateTime 数据类型
采纳答案by Mark Smith
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).
SQLite 没有专门的日期时间类型，但有一些日期时间函数。遵循这些函数理解的字符串表示格式（实际上只有格式 1-10）（将值存储为字符串）然后您可以使用它们，加上字符串上的字典比较将匹配日期时间比较（只要您不尝试将日期与时间或日期时间与时间进行比较，这无论如何都没有多大意义）。
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.)
根据您使用的语言，您甚至可以获得自动转换。（这不适用于示例中的 SQL 语句中的比较，但会让您的生活更轻松。）
回答by Mark Smith
To solve this problem, I store dates as YYYYMMDD. Thus,
where mydate >= '20090101' and mydate <= '20050505'
为了解决这个问题，我将日期存储为YYYYMMDD. 因此，
where mydate >= '20090101' and mydate <= '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.
它一直都很正常。您可能只需要编写一个解析器来处理用户输入日期的方式，以便您可以将它们转换为YYYYMMDD.
回答by Simon
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)
回答by ifredy
The following is working fine for me using SQLite:
以下使用 SQLite 对我来说工作正常：
SELECT *
FROM ingresosgastos
WHERE fecharegistro BETWEEN "2010-01-01" AND "2013-01-01"
回答by Hardeep Singh
Sqlite can not compare on dates. we need to convert into seconds and cast it as integer.
Sqlite 无法比较日期。我们需要转换为秒并将其转换为整数。
Example
例子
SELECT * FROM Table
WHERE
CAST(strftime('%s', date_field) AS integer) <=CAST(strftime('%s', '2015-01-01') AS integer) ;
回答by Jose Jithin Stanly
Following worked for me.
以下对我来说有效。
SELECT *
FROM table_log
WHERE DATE(start_time) <= '2017-01-09' AND DATE(start_time) >= '2016-12-21'
回答by Lyall Pearce
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 includedbut any data aftermidnight on the 15th was not. If their query was,
要记住的关键是，最初的海报排除了 2009-11-15 00:00:00 之后的所有数据。因此，包括在 15 日午夜记录的任何数据，但不包括15 日午夜之后的任何数据。如果他们的查询是
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.
为了清楚起见，使用 between 子句。
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 :)
它会稍微好一点。它仍然没有考虑闰秒，其中一个小时实际上可以超过 60 秒，但足以在这里讨论:)
回答by Summved Jain
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
我的日期以 MM/DD/YYYY HH:MM 格式存储，因此必须以该格式进行比较
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');
下面的查询比较了将日期转换为 MM/DD/YYY 格式并获取从过去五天到今天的数据。BETWEEN 运算符会有所帮助，您可以简单地指定开始日期和结束日期。
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'));下面的查询将使用大于运算符 (>)。
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
总和
回答by 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:
我必须将时间与时区信息一起存储在其中，并且能够使用以下格式进行查询：
"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
回答by J. Polfer
You could also write up your own user functionsto 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.
您还可以编写自己的用户函数来以您选择的格式处理日期。SQLite 有一个相当简单的方法来编写你自己的用户函数。例如，我写了一些将持续时间加在一起。
