sql

统计相反条件的数量

select m.mf_id, count(case when `is_complete` = 0 then 1 else null end), count(case when `is_complete` = 1 then 1 else null end) from manifest m inner join task t on t.mf_id = m.mf_id  where t.is_complete = 0 group by m.mf_id

sqlite将毫秒时间戳转为日期

SELECT date(1469528510300/1000, 'unixepoch', 'localtime');
[color=red]2016-07-26[/color]

将时间戳格式化成年月日

select * from task group by strftime('%Y%m%d ', datetime(end_time/1000, 'unixepoch'))
[color=red]20160905 [/color]

将标准时间戳转为本地格式的时间

strftime('%Y%m%d %H:%M:%S', datetime(end_time/1000, 'unixepoch'), 'localtime')

SELECT strftime('%Y%m%d %H:%M:%S', datetime(end_time/1000, 'unixepoch'), 'localtime') as t_date, t.uid, t.task_name, t.task_desc, m.uid, t.creator_id, t.executor_id, t.order_num, t.end_time, t.is_star, t.create_time, t.is_exist_attach, 
t.is_exist_comment, m.mf_name FROM manifest m INNER JOIN task t ON m.mf_id = t.mf_id where 1 = 1 and t.is_deleted = 0 and t.is_complete = 0 
and t.end_time > 1473465600000 and t.end_time < 1533945600000 and t.order_num > 0

[color=red]20160912 13:20:20[/color]