SQLite中正确类型转换方法及datetime转换截断问题咨询

嘿，我来帮你搞定这两个SQLite的问题，都是实际开发中容易踩的坑，我给你详细拆解下：

首先得明确：SQLite是动态类型系统，和MySQL、PostgreSQL这类强类型数据库不一样，它的CAST操作主要用来做显式的类型转换，语法很简单：

CAST(表达式 AS 目标类型)

这里的目标类型只能是SQLite官方支持的5种：TEXT、INTEGER、REAL、NUMERIC、BLOB，其他类型（比如BOOLEAN、DATETIME）都不是合法的目标类型，会被SQLite自动映射到相近的类型。

给你几个常用场景的例子：

• 字符串转整数：如果字符串是合法数字，会直接转成整数；如果不是，返回0。

  SELECT CAST('12345' AS INTEGER); -- 返回12345
  SELECT CAST('abc123' AS INTEGER); -- 返回0
  

• 数字转字符串：把数值类型转成文本格式，方便拼接或格式化。

  SELECT CAST(67.89 AS TEXT); -- 返回"67.89"
  

• 字符串转实数：处理带小数的数字字符串。

  SELECT CAST('90.12' AS REAL); -- 返回90.12
  

• NUMERIC类型转换：这是一个通用类型，SQLite会根据表达式内容自动选择最合适的类型（整数或实数）。

  SELECT CAST('123' AS NUMERIC); -- 返回整数123
  SELECT CAST('45.67' AS NUMERIC); -- 返回实数45.67
  

注意：SQLite没有BOOLEAN类型，如果你尝试CAST(1 AS BOOLEAN)，实际会转成INTEGER类型的1，因为SQLite会把不支持的类型映射到最接近的原生类型。

你遇到的CAST("2017-04-23 9:12:08 PM" AS DATETIME)只返回2017，核心原因是：SQLite根本没有原生的DATETIME类型！

当你写CAST(xxx AS DATETIME)时，SQLite会把这个不合法的目标类型自动映射为NUMERIC类型。而把带-的日期字符串转成NUMERIC时，SQLite只会读取到第一个非数字字符（也就是-）之前的部分，所以就只返回了2017，这就是截断的根源。

那正确的处理方式是什么？用SQLite专门的日期时间函数，比如datetime()或strftime()，它们能正确解析带AM/PM的12小时制格式：

• 转成标准的UTC时间格式：

  SELECT datetime("2017-04-23 9:12:08 PM") AS dt; -- 返回2017-04-23 21:12:08
  

• 自定义输出格式（比如保留12小时制或者其他格式）：

  -- 转成24小时制的字符串格式
  SELECT strftime("%Y-%m-%d %H:%M:%S", "2017-04-23 9:12:08 PM") AS dt;
  -- 或者保留12小时制带AM/PM的格式
  SELECT strftime("%Y-%m-%d %I:%M:%S %p", "2017-04-23 9:12:08 PM") AS dt;
  

记住：在SQLite里，日期时间都是以TEXT、REAL（Julian日）或INTEGER（时间戳）的形式存储的，没有专门的日期类型，处理日期时间一定要用内置的日期函数，别用CAST来碰运气。

内容的提问来源于stack exchange，提问作者junkone