SQLite 日期时间比较 [英] SQLite DateTime comparison

问题描述

我似乎无法从使用日期时间字符串作为比较的 sqlite 数据库的查询中获得可靠的结果:

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'

我应该如何处理与 sqlite 的日期时间比较?

how should I handle datetime comparisons to sqlite?

更新:字段 mydate 是 DateTime 数据类型

update: field mydate is a DateTime datatype

推荐答案

SQLite 没有专用的日期时间 类型，但确实有一个几个日期时间函数.遵循这些函数理解的字符串表示格式(实际上只有格式 1-10)(将值存储为字符串)然后你可以使用它们，加上字符串上的字典比较将匹配日期时间比较(只要你不尝试将日期与时间或日期时间与时间进行比较，这无论如何都没有多大意义).

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

根据您使用的语言，您甚至可以获得自动转换.(这不适用于示例中的 SQL 语句中的比较，但会让您的生活更轻松.)

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

这篇关于SQLite 日期时间比较的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！