如果对于sqlite不熟悉的同学, 请移步:
http://www.runoob.com/sqlite/sqlite-tutorial.html(菜鸟教程),基础的关于sqlite的东西这边都可以找到
1.sqlite中时间函数
参见:http://www.runoob.com/sqlite/sqlite-date-time.htm
2.查询本周数据
select * from 表名 where 字段名 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')
3.查询本月的数据
select * from 表名 where 字段名 between datetime('now','start of month',' 1 second') and
datetime('now','start of month',' 1 month','-1 second')
4.查询最近7的值(从当前起向前推6天,包括今天)
select * from 表 名 where 时间字段 + between date('now','start of day','-6day') and date('now')
5.查询最近一年的数据(从这个月起向前推12个月)
select * from 表 名 where 时间字段 between date('now','start of month','-12 month and date('now')
注意:date 和datetime所对应的时间格式不一致,存储的字段必须严格按照你想要用的时间函数的格式存储,否则会有坑,时间格式具体见1;以上几种经测试有效,如有问题,可能是使用的姿势有问题,换个姿势再来一次,肯定可以的
举例 表名:ticket_order_detail
-- 今天 select * from ticket_order_detail where to_days(use_time) = to_days(now()); -- 7天 SELECT *FROM ticket_order_detail where DATE_SUB(CURDATE(), INTERVAL 7 DAY) <= date( use_time) -- 近30天 SELECT *FROM ticket_order_detail where DATE_SUB(CURDATE(), INTERVAL 30 DAY) <= date( use_time) -- 本月 SELECT *FROM ticket_order_detail WHERE DATE_FORMAT( use_time, '%Y%m' ) = DATE_FORMAT( CURDATE( ) , '%Y%m' ) -- 上一月 SELECT *FROM ticket_order_detail WHERE PERIOD_DIFF( date_format( now( ) , '%Y%m' ) , date_format( use_time, '%Y%m' ) ) =1 #查询本季度数据 select * from `ticket_order_detail` where QUARTER(use_time)=QUARTER(now()); #查询上季度数据 select * from `ticket_order_detail` where QUARTER(use_time)=QUARTER(DATE_SUB(now(),interval 1 QUARTER)); #查询本年数据 select * from `ticket_order_detail` where YEAR(use_time)=YEAR(NOW()); #查询上年数据 select * from `ticket_order_detail` where year(use_time)=year(date_sub(now(),interval 1 year)); -- 查询当前这周的数据 SELECT name,submittime FROM enterprise WHERE YEARWEEK(date_format(submittime,’%Y-%m-%d’)) = YEARWEEK(now()); -- 查询上周的数据 SELECT name,submittime FROM enterprise WHERE YEARWEEK(date_format(submittime,’%Y-%m-%d’)) = YEARWEEK(now())-1; -- 查询当前月份的数据 select name,submittime from enterprise where date_format(submittime,’%Y-%m’)=date_format(now(),’%Y-%m’) -- 查询距离当前现在6个月的数据 select name,submittime from enterprise where submittime between date_sub(now(),interval 6 month) and now(); -- 查询上个月的数据 select name,submittime from enterprise where date_format(submittime,’%Y-%m’)=date_format(DATE_SUB(curdate(), INTERVAL 1 MONTH),’%Y-%m’) select*from`user`whereDATE_FORMAT(pudate,‘%Y%m‘)=DATE_FORMAT(CURDATE(),‘%Y%m‘) ; select * from user where WEEKOFYEAR(FROM_UNIXTIME(pudate,’%y-%m-%d’)) = WEEKOFYEAR(now()) select* fromuser whereMONTH(FROM_UNIXTIME(pudate,‘%y-%m-%d‘))=MONTH(now()) select* from[user] whereYEAR(FROM_UNIXTIME(pudate,‘%y-%m-%d‘))=YEAR(now()) andMONTH(FROM_UNIXTIME(pudate,‘%y-%m-%d‘))=MONTH(now()) select* from[user] -- wherepudatebetween上月最后一天 -- and下月第一天 where date(regdate) = curdate(); select * from test where year(regdate)=year(now()) and month(regdate)=month(now()) and day(regdate)=day(now()) SELECT date( c_instime ) ,curdate( ) FROM `t_score` WHERE 1 LIMIT 0 , 30
Android 数据查询query函数参数解析
我们下面举个例子,假设有如下数据表,表名"Orders":
假设我们想查询客户总的订单数在500元以上的,且County在中国的客户的名称和订单总数,且按照CustomerName来排序,默认ASC排序,那么SQL语句应当是:
Sql代码
- SELECT CustomerName, SUM (OrderPrice) FROM Orders WHERE Country=?
- GROUP BY CustomerName
- HAVING SUM (OrderPrice)>500
- ORDER BY CustomerName
那么对应Android的query函数的参数如下:
Java代码
- String table = "Orders" ;
- String[] columns = new String[] { "CustomerName" , "SUM(OrderPrice)" };
- String selection = "Country=?" ;
- String[] selectionArgs = new String[]{ "China" };
- String groupBy = "CustomerName" ;
- String having = "SUM(OrderPrice)>500" ;
- String orderBy = "CustomerName" ;
- Cursor c = db.query(table, columns, selection, selectionArgs, groupBy, having, orderBy, null );
查询的结果应该是: