20210729_sqlite中13位时间戳的处理
1. sqlite字符串拼接, 使用的是 || 双竖线;
2. sqlite数字转字符串, cast( 数字 as TEXT) 或CONVERT（TEXT，’字段名‘）
3. sqlite不支持13位时间戳的转换
4. 10位时间戳的转是 : datetime(1443148382, 'unixepoch', 'localtime') ; 13位转出来是个null
1 SELECT b.id AS 序号, 2 p.jizhong AS 机种, 3 p.gudingma AS 固定码, 4 p.zhuangxiangshu AS 装箱数, 5 p.lianpianshu AS 连片数, 6 b.barcode AS 条码, 7 CASE b.barcode_from WHEN '0' THEN '扫码' ELSE '录入' END AS 来源, 8 --一行写的话别考虑什么性能了, 本身sqlite就是轻量级数据库 9 datetime(b.create_time / 1000, 'unixepoch', 'localtime') || '.' || CAST (CASE WHEN LENGTH(CAST (b.create_time % 1000 AS TEXT) ) = 2 THEN ('0' || CAST (b.create_time % 1000 AS TEXT) ) ELSE b.create_time % 1000 END AS TEXT) AS 时间, 10 11 12 b.waixiangma AS 外箱码 13 FROM BarcodeCount AS b, 14 PARAMETERSET AS p 15 WHERE b.jizhongid = p.id AND 16 b.create_time BETWEEN 1627488000000 AND 1627574399000;
