SQLITE 日期格式转换

日期格式

从 TIME列（unix秒数） 读取时间

SELECT TIME from TableName

或

SELECT strftime('%s', TIME,'unixepoch')   as TIME from TableName

从 TIME列（unix秒数）转换成 %Y-%m-%d 

SELECT  strftime('%Y-%m-%d', TIME,'unixepoch') as TIME from TableName

从 TIME列（unix秒数.SSS）转换成 毫秒

SELECT cast(substr(strftime('%f', TIME) ,-3) as real) +cast(strftime('%s', TIME)  as real)* 1000.0 as TIME, from TableName

日期筛选

WHERE TIME BETWEEN "2023-10-28 08:00:00" AND  "2023-11-20 09:50:00"

或者

where TIME > strftime('%s', '2023-11-19 15:36:34') AND TIME < strftime('%s', '2023-11-20 00:36:34','+10 hour');