什么来处理DateTime对象的最有效的方法，尤其是因为DATETIME结果在数字列？ [英] What's the most efficient way to handle DateTimes, especially since a column of DATETIME results in a NUMERIC?
问题描述
有答案遍布计算器是引用文档的同一节（如下）：
There are answers all over StackOverflow that quote the same section of the documentation (as follows):
SQLite没有一个存储类存储日期和/或时间预留。相反，SQLite的的内置日期和时间函数能够存储日期和时间为TEXT，REAL或INTEGER值：
SQLite does not have a storage class set aside for storing dates and/or times. Instead, the built-in Date And Time Functions of SQLite are capable of storing dates and times as TEXT, REAL, or INTEGER values:
- TEXT为ISO8601字符串（YYYY-MM-DD HH：MM：SS.SSS）。
- REAL为儒略日的数字，因为中午在格林威治天11月24日的数字，公元前4714根据proleptic公历。
- 整数作为Unix的时间，因为1970-01-01 00:00:00 UTC的秒数。
应用程序可以选择存储日期和时间的任何格式，并自由地使用格式之间进行转换的内置日期和时间函数。
Applications can chose to store dates and times in any of these formats and freely convert between formats using the built-in date and time functions.
不过，我的认为的一些人（问题提问者并回答-ERS）感到困惑或省略的关于表的创建，其中的DateTime实际上是一个数字内部类型，应该作为一个 DATETIME 或
However I think that some people (question askers and answer-ers) are confused about or omit the following detail regarding table creation, where DateTime is actually a NUMERIC type internally, and should be created as a DATETIME or NUMERIC to avoid performance issues relating to casting.
我可能误解的东西，但我确实看到答案在那里告诉人们创造一个日期时间TEXT列，这似乎效率不高。
I may be misunderstanding something, but I do see answers out there telling people to create a TEXT column for datetime, which doesn't seem efficient.
问
既然我可以查询日期时间列TEXT，REAL或INTEGER，什么是处理日期时间的最有效的方法，尤其是因为DATETIME结果在数字列。
Given that I can query a datetime column as TEXT, REAL, or INTEGER, what is the most efficient way to handle datetimes, especially since a column of DATETIME results in a NUMERIC.
-
请问的数字/日期时间导致铸造延误，由于TEXT，REAL或整数比较的columnType？
Does a columnType of NUMERIC/DateTime result in casting delays due to comparisons of TEXT, REAL, or Integer?
能否将TEXT列类型和SQLite的文本查询类型会更快？
Could a TEXT column type and a TEXT query type for SQLite be faster?
能一列REAL类型和SQLite的一个真正的查询类型会更快？
Could a REAL column type and a REAL query type for SQLite be faster?
一个能INTEGER列类型和SQLite的一个INTEGER查询类型会更快？
Could a INTEGER column type and a INTEGER query type for SQLite be faster?
我将如何转换成DATETIME数字更快的查询？
How would I convert a DATETIME into a NUMERIC for faster queries?
推荐答案
这些问题presuppose事实，不是真的。
These questions presuppose facts which are not actually true.
的SQLite没有列类型。只有列亲和力，该做的不的限制哪些类型实际上可以存储在列
SQLite has no column types. There are only column affinities, which do not restrict what types can be actually stored in a column.
有没有什么特别的DATETIME类型。当文档中提到TEXT和REAL和INTEGER，这意味着你实际上必须使用您的值的那些类型之一。一旦你正在使用一个这样的值时，SQLite不会自动将其转换成另一种类型的，因为它不知道这个值是日期/时间值。
There is no special DATETIME type. When the documentation mentions TEXT and REAL and INTEGER, it means that you actually have to use one of those types for your values. And once you are using one such value, SQLite will not automatically convert it into another type, because it does not know that this value is a date/time value.
请问的数字/日期时间导致铸造延误columnType由于TEXT的比较，REAL或整型？
Does a columnType of NUMERIC/DateTime result in casting delays due to comparisons of TEXT, REAL, or Integer?
您获得铸件的延迟只有当你有在列中存储一种类型的值，并且对另一类型的比较。
（像一个字符串1970-01-01是从不的平等与一些像 0 ，所以这种比较是没有意义的。）
You get casting delays only when you have values of one type stored in the column, and are comparing against another type.
(And a string like '1970-01-01' is never equal with a number like 0, so such comparisons would not make sense.)
能否将TEXT列类型和SQLite的文本查询类型会更快？
Could a TEXT column type and a TEXT query type for SQLite be faster?
列的类型并不重要。
如果您存储在列TEXT值，你的必须的用一个文本值查询。结果
如果您存储在列REAL值，你的必须的查询与REAL值。结果
如果您存储在列整数值，您的必须的一个整数查询。
If you store TEXT values in the column, you must query with TEXT values.
If you store REAL values in the column, you must query with REAL values.
If you store INTEGER values in the column, you must query with INTEGER values.
我将如何转换成DATETIME数字更快的查询？
How would I convert a DATETIME into a NUMERIC for faster queries?
有没有DATETIME类型。
There is no DATETIME type.
这篇关于什么来处理DateTime对象的最有效的方法，尤其是因为DATETIME结果在数字列？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
