sqlite 中的日期转换（原创）

原创作品，如有转载请注明来源。

sqlite中没有专门的日期数据类型，联机文档中只提到日期数据可保存在 INTEGER 列中，并没有给出详细的例子。经过尝试，在SQL语句中可使用 sqlite 的内置函数进行 integer 日期值到字符串的转换。下面以 MySQL 版本的表转换成 sqlite 版本为例，希望对初学者有所帮助。假设 MySQL 表中有以下三列：

列名	MySQL数据类型	sqlite数据类型
OptStamp	DateTime	INTEGER
OptDate	Date	INTEGER
OptTime	Time	INTEGER

1. 字符串类型的日期转换成 Unix 时间戳

-- '%s' 参数表示将字符串表示的日期或时间转换成从 1970-01-01 算起的秒数，即 Unix 时间戳
select strftime('%s', '2004-08-19 18:51:06');

返回 1092941466

插入语句对比

-- MySQL
insert into tb(OptStamp) values('2004-08-19 18:51:06');
insert into tb(OptDate) values('2004-08-19');
insert into tb(OptTime) values('18:51:06');

-- sqlite
insert into tb(OptStamp) values(strftime('%s', '2004-08-19 18:51:06')); -- 将日期+时间写入 integer 列中
insert into tb(OptDate) values(strftime('%s', '2004-08-19'));           -- 将日期写入 integer 列中
insert into tb(OptTime) values(strftime('%s', '18:51:06'));             -- 将时间写入 integer 列中

2. Unix 时间戳转换成字符串

select datetime(1092941466, 'unixepoch');

返回 2004-08-19 18:51:06

查询语句对比

-- MySQL
select OptStamp from tb;  -- 返回 '2004-08-19 18:51:06'
select OptDate from tb;   -- 返回 '2007-09-04'
select OptTime from tb;   -- 返回 '18:51:06'

-- sqlite
select datetime(OptStamp, 'unixepoch') from tb;  -- 读取 integer 列，返回 '2004-08-19 18:51:06'
select date(OptDate, 'unixepoch') from tb;       -- 读取 integer 列，返回 '2004-08-19'
select time(OptTime, 'unixepoch') from tb;       -- 读取 integer 列，返回 '18:51:06'

需要注意的问题

默认情况下 sqlite 认为用户输入的时间戳是 UTC 时间，如果有需要可以强制其转化为本地时间：

-- 将本地时间字符串转化成时间戳（UTC时间），返回 1092912666
select strftime('%s', '2004-08-19 18:51:06', 'utc');
-- 将 UTC 时间戳转换成本地时间字符串，返回 '2004-08-19 18:51:06'
SELECT datetime(1092912666, 'unixepoch', 'localtime');

参考文献：
python 的时间函数
SQLite 日期 & 时间