Sqlite sql查询数据数据类型转换

一、SQL数据类型转换代码

SELECT CAST('3.02' as decimal)
-- they aren't real decimals in sqlite, though. beware floats.

二、sqlite数据类型转换参考

Affinity of type-name

Conversion Processing 转换处理

NONE

没有

Casting a value to a type-name with no affinity causes the value to be converted into a BLOB. Casting to a BLOB consists of first casting the value to TEXT in the encoding of the database connection, then interpreting the resulting byte sequence as a BLOB instead of as TEXT.

将一个值转换为一个 类型名称 没有关联将导致值转换为BLOB。强制转换为BLOB包括首先将值强制转换为 编码 然后将结果字节序列解释为BLOB而不是文本。

TEXT

文本

To cast a BLOB value to TEXT, the sequence of bytes that make up the BLOB is interpreted as text encoded using the database encoding.

Casting an INTEGER or REAL value into TEXT renders the value as if via sqlite3_snprintf() except that the resulting TEXT uses the encoding of the database connection.

若要将BLOB值强制转换为文本，组成BLOB的字节序列将被解释为使用数据库编码的文本编码。将整数值或实数值强制转换为文本将使该值如同通过 sqlite3\u snprintf（） 除了结果文本使用 编码 数据库连接的名称。

REAL

真

When casting a BLOB value to a REAL, the value is first converted to TEXT.

When casting a TEXT value to REAL, the longest possible prefix of the value that can be interpreted as a real number is extracted from the TEXT value and the remainder ignored. Any leading spaces in the TEXT value are ignored when converging from TEXT to REAL. If there is no prefix that can be interpreted as a real number, the result of the conversion is 0.0.

将BLOB值强制转换为实数时，首先将该值转换为文本。

将文本值强制转换为实数时，将从文本值中提取可解释为实数的值的最长前缀，并忽略其余部分。从文本聚合到实数时，将忽略文本值中的任何前导空格。如果没有可以解释为实数的前缀，则转换的结果为0.0。

INTEGER

整数

When casting a BLOB value to INTEGER, the value is first converted to TEXT.

When casting a TEXT value to INTEGER, the longest possible prefix of the value that can be interpreted as an integer number is extracted from the TEXT value and the remainder ignored. Any leading spaces in the TEXT value when converting from TEXT to INTEGER are ignored. If there is no prefix that can be interpreted as an integer number, the result of the conversion is 0. If the prefix integer is greater than +9223372036854775807 then the result of the cast is exactly +9223372036854775807. Similarly, if the prefix integer is less than -9223372036854775808 then the result of the cast is exactly -9223372036854775808.

When casting to INTEGER, if the text looks like a floating point value with an exponent, the exponent will be ignored because it is no part of the integer prefix. For example, “(CAST ‘123e+5’ AS INTEGER)” results in 123, not in 12300000.

The CAST operator understands decimal integers only — conversion of hexadecimal integers stops at the “x” in the “0x” prefix of the hexadecimal integer string and thus result of the CAST is always zero.

A cast of a REAL value into an INTEGER results in the integer between the REAL value and zero that is closest to the REAL value. If a REAL is greater than the greatest possible signed integer (+9223372036854775807) then the result is the greatest possible signed integer and if the REAL is less than the least possible signed integer (-9223372036854775808) then the result is the least possible signed integer.

Prior to SQLite version 3.8.2 (2013-12-06), casting a REAL value greater than +9223372036854775807.0 into an integer resulted in the most negative integer, -9223372036854775808. This behavior was meant to emulate the behavior of x86/x64 hardware when doing the equivalent cast.

将BLOB值强制转换为整数时，首先将该值转换为文本。

将文本值强制转换为整数时，将从文本值中提取可解释为整数的值的最长前缀，并忽略余数。从文本转换为整数时，文本值中的任何前导空格都将被忽略。如果没有可解释为整数的前缀，则转换结果为0。如果前缀整数大于+9223372036854775807，则强制转换的结果正好是+9223372036854775807。类似地，如果前缀整数小于-9223372036854775808，则强制转换的结果正好是-9223372036854775808。

强制转换为整数时，如果文本看起来像带指数的浮点值，则指数将被忽略，因为它不是整数前缀的一部分。例如，”（将’123e+5’转换为整数）”结果是123，而不是12300000。

CAST运算符只理解十进制整数-的转换 十六进制整数 在十六进制整数字符串的”0x”前缀中的”x”处停止，因此强制转换的结果始终为零。

将实值转换为整数将导致实值和零之间的整数最接近实值。如果实数大于最大可能有符号整数（+9223372036854775807），则结果为最大可能有符号整数，如果实数小于最小可能有符号整数（-9223372036854775808），则结果为最小可能有符号整数。

在SQLite版本3.8.2（2013-12-06）之前，将大于+9223372036854775807.0的实值强制转换为整数会导致最负的整数-9223372036854775808。此行为旨在模拟执行等效转换时x86/x64硬件的行为。

NUMERIC

数字

Casting a TEXT or BLOB value into NUMERIC yields either an INTEGER or a REAL result. If the input text looks like an integer (there is no decimal point nor exponent) and the value is small enough to fit in a 64-bit signed integer, then the result will be INTEGER. Input text that looks like floating point (there is a decimal point and/or an exponent) and the text describes a value that can be losslessly converted back and forth between IEEE 754 64-bit float and a 51-bit signed integer, then the result is INTEGER. (In the previous sentence, a 51-bit integer is specified since that is one bit less than the length of the mantissa of an IEEE 754 64-bit float and thus provides a 1-bit of margin for the text-to-float conversion operation.) Any text input that describes a value outside the range of a 64-bit signed integer yields a REAL result.

Casting a REAL or INTEGER value to NUMERIC is a no-op, even if a real value could be losslessly converted to an integer.

将文本或BLOB值转换为数字将产生整数或实数结果。如果输入文本看起来像一个整数（没有小数点或指数），并且该值足够小，可以容纳64位有符号整数，那么结果将是整数。输入看起来像浮点的文本（有一个小数点和/或一个指数），该文本描述了一个值，该值可以在IEEE 754 64位浮点和51位有符号整数之间无损地来回转换，然后结果是整数(在前面的句子中，指定了一个51位整数，因为它比ieee754 64位浮点的尾数长度少一位，因此为文本到浮点的转换操作提供了1位的余量。）任何描述64位有符号整数范围之外的值的文本输入都会产生一个实际结果。

将实数或整数值转换为数值是不可操作的，即使实数可以无损地转换为整数。

decimal

十进制

因为sqlite为弱引用，使用字段前将他强制转为日期型，用datetime。或者最原始的 strftime。

SELECT distinct ID from testTable where datetime(availDate) between datetime(‘2015-01-12 04:00’) and datetime(‘2015-01-13 00:00’);

SQLite支持以下五个日期时间函数：

1. date( timestring, modifier, modifier, … )
2. time( timestring, modifier, modifier, … )
3. datetime( timestring, modifier, modifier, … )
4. julianday( timestring, modifier, modifier, … )
5. strftime( format, timestring, modifier, modifier, … )

这五个函数都是以时间字符窜(timestring)为参数，同时，时间字符窜后面还可以接收零个或者多个修饰符。另外，strftime()函数与其它的四个函数有点区别，它还可以接收一个字符窜格式（与C函数中printf函数使用字符窜格式）。

SQLite的日期时间函数使用的是ISO-8601日期时间格式规范中的子集。date()函数返回的日期格式为：YYYY-MM-DD，time()函数返回的时间格式为：HH:MM:SS，datetime()函数返回的格式为”YYYY-MM-DD HH:MM:SS”。julianday()返回的是儒略日期（Julian day）——从公元前4714年11月24日正午（以格林威治时间为准）至当前的天数。strftime()函数则按照用户指定的格式字符窜（第一个参数）来格式化用户的时间输入（第二个参数）。格式字符窜的组成与C函数中的格式化参数基本相似，但表示的意义却不相同。主要如下：
%d day of month: 00
%f fractional seconds: SS.SSS
%H hour: 00-24
%j day of year: 001-366
%J Julian day number
%m month: 01-12
%M minute: 00-59
%s seconds since 1970-01-01
%S seconds: 00-59
%w day of week 0-6 with sunday==0
%W week of year: 00-53
%Y year: 0000-9999
%% %

另外，所有其它日期时间函数能够表达的格式，strftime()函数都可以做，如下：
Function Equivalent strftime()
date(…) strftime(‘%Y-%m-%d’, …)
time(…) strftime(‘%H:%M:%S’, …)
datetime(…) strftime(‘%Y-%m-%d %H:%M:%S’, …)
julianday(…) strftime(‘%J’, …)

但是，之所以还提供了strftime之外的其它函数，则完全是从便捷与性能上的考虑。

时间字符窜（Time String）

时间字符窜可以以下的任意一种形式：

1. YYYY-MM-DD
2. YYYY-MM-DD HH:MM
3. YYYY-MM-DD HH:MM:SS
4. YYYY-MM-DD HH:MM:SS.SSS
5. YYYY-MM-DD T HH:MM
6. YYYY-MM-DD T HH:MM:SS
7. YYYY-MM-DD T HH:MM:SS.SSS
8. HH:MM
9. HH:MM:SS
10. HH:MM:SS.SSS
11. now
12. DDDDDDDDDD

在第5至7中，T是用来分隔日期时间的（参照ISO-8601）。8至10指定的是时间格式，由于没有日期输入，这几个格式默认日期为2000-01-01。第11条中，’now’ 将被转化为当前的日期时间。

修饰符(Modifiers)

时间字符窜(time string)后台可以加上0个或者多个修饰符用以辅助改变时间字符窜。每一个修饰符都是对其左边值的转换，当有多个修饰符时，其生效的顺序为从左至右。可用的修改符有：

1. NNN days
2. NNN hours
3. NNN minutes
4. NNN.NNNN seconds
5. NNN months
6. NNN years
7. start of month
8. start of year
9. start of day
10. weekday N
11. unixepoch
12. localtime
13. utc

前面6个修饰符是对时间字符窜及其前面的修饰符处理后的时间结果进行增加或者减少。例如，对于YYYY-MM-DD格式的时间，当使用”±NNN months”修饰符时，则是对MM增加/减少相应的月数。

例子

当前日期
SELECT date(‘now’);

当月的最后一天
SELECT date(‘now’,’start of month’,’+1 month’,’-1 day’);

将UNIX时间戳转化为时间日期格式
SELECT datetime(1092941466, ‘unixepoch’);

将UNIX时间戳转化为本地时间
SELECT datetime(1092941466, ‘unixepoch’, ‘localtime’);

当前日期的UNIX时间戳格式
SELECT strftime(‘%s’,’now’);

计算当天与美国独立日之间的日期差（以天为单位）
SELECT julianday(‘now’) – julianday(‘1776-07-04′);

计算任意时间至当前时间的时间差（以秒为单位）
SELECT strftime(‘%s’,’now’) – strftime(‘%s’,’2004-01-01 02:34:56′);

将日期确定在本年度十月的条一个星期二
SELECT date(‘now’,’start of year’,’+9 months’,’weekday 2′);

Original: https://blog.csdn.net/qh0526wy/article/details/116378789
Author: qh0526wy
Title: Sqlite sql查询数据数据类型转换