sqlite 获取当天当周当月等数据

来源：互联网发布：内网渗透端口转发编辑：程序博客网时间：2024/06/06 02:27

----昨天select * from 表 where Time>=datetime('now','start of day','-1 day') and Time<datetime('now','start of day','+0 day')----当天select * from 表 where Time>=datetime('now','start of day','+0 day') and Time<datetime('now','start of day','+1 day')----当周select  * from 表 where Time>=datetime('now','start of day','-7 day','weekday 1') AND Time<datetime('now','start of day','+0 day','weekday 1')----当月select * from 表 where Time>=datetime('now','start of month','+0 month','-0 day') AND Time < datetime('now','start of month','+1 month','0 day')----上月select * from 表 where Time>=datetime('now','start of month','-1 month','-0 day') AND Time <datetime('now','start of month','+0 month','-1 day')

上面的“当周”和“当月”的获取方法，知识取最近7（30）天，并非本周（本月）。

下面是经过测试可以用的方法：

--本月select *from UserConsumewhere rowTime between datetime('now','start of month','+1 second') and datetime('now','start of month','+1 month','-1 second') --本周select *from UserConsumewhere rowTime between datetime(date(datetime('now',strftime('-%w day','now'))),'+1 second') and datetime(date(datetime('now',(6 - strftime('%w day','now'))||' day','1 day')),'-1 second')

----上面本周的计算再次出错，再次修正：

--start
select datetime("now","start of day","-6 day","weekday 1")

--end
select datetime(datetime("now","start of day","weekday 0"),"start of day","+1 day")

0 0