一. Java获取一段时间有几个星期一
import java.text.DateFormat;
import java.util.Calendar;
/**
* 给定时间段和星期几，计算该时间段内共有多少个给定的星期几
* @param start 开始时间,格式yyyy-MM-dd
* @param end 结束时间，格式yyyy-MM-dd
* @param a 星期几，从星期一到星期天，分别用数字1-7表示
* @return 星期几统计数
*/
private long weekend(String start,String end,int a){
DateFormat format = new SimpleDateFormat("yyyy-MM-dd");
long sunDay = 0;//计数
try{
Calendar startDate = Calendar.getInstance(); //开始时间
startDate.setTime(format.parse(start));
Calendar endDate = Calendar.getInstance();//结束时间
endDate.setTime(format.parse(end));
int SW = startDate.get(Calendar.DAY_OF_WEEK)-1;//开始日期是星期几
int EW = endDate.get(Calendar.DAY_OF_WEEK)-1;//结束日期是星期几
long diff = endDate.getTimeInMillis()-startDate.getTimeInMillis();
long days = diff / (1000 * 60 * 60 * 24);//给定时间段内一共有多少天
long w = Math.round(Math.ceil(((days+SW+(7-EW))/7.0)));//给定时间内，共有多少个星期
sunDay = w;//总的星期几统计数
if(a<SW)//给定的星期几小于起始日期的星期几，需要减少一天
sunDay--;
if(a>EW)//给定的星期几大于结束日期的星期几，需要减少一天
sunDay--;
}catch(Exception se){
se.printStackTrace();
}
return sunDay;
}
二. postgreSQL按天分组
1. 按天分组
SELECT date_trunc('day', hpdr.test_time) AS "day" , count(*) AS "sum_count"
FROM health_patient_device_result hpdr
WHERE hpdr.test_time > now() - interval '3 months'
GROUP BY 1
ORDER BY 1;
2. 按周分组
SELECT date_trunc('week', loggedin) AS "Week" , count(*) AS "No. of users"
FROM logins
WHERE created > now() - interval '3 months'
GROUP BY 1
ORDER BY 1;
3. 按月分组
SELECT date_trunc('month', loggedin) AS "Month" , count(*) AS "No. of users"
FROM logins
WHERE created > now() - interval '1 year'
GROUP BY 1
ORDER BY 1;
4. 显示近10个月年月
SELECT
to_char( date_trunc( 'month', YearMonth ), 'YYYY-MM' )
FROM
generate_series ( CURRENT_DATE - INTERVAL '10 month', CURRENT_DATE, '1 month' :: INTERVAL ) YearMonth;
三. PostgreSQL：COALESCE函数
COALESCE函数是返回参数中的第一个非null的值，它要求参数中至少有一个是非null的，如果参数都是null会报错。
select COALESCE(null,null); -- 报错
select COALESCE(null,null,now(),''); -- 结果会得到当前的时间
select COALESCE(null,null,'',now()); -- 结果会得到''
-- 可以和其他函数配合来实现一些复杂点的功能：查询学生姓名，如果学生名字为null或''则显示“姓名为空”
select case when coalesce(name,'') = '' then '姓名为空' else name end from student;
四. postgresql中将时间减1秒
select '2014-04-14 12:17:55.772'::timestamp - interval '1 seconds';
五. 存JSON格式字段取值
1. 取值
-- "->>" 通过键获得 JSON 对象域 结果为text
select '{"nickname": "goodspeed", "avatar": "avatar_url", "tags": ["python", "golang", "db"]}'::json->>'nickname' as nickname;
-- "->" 通过键获得 JSON 对象域 结果为json对象
select '{"nickname": "goodspeed", "avatar": "avatar_url", "tags": ["python", "golang", "db"]}'::json->'nickname' as nickname;
2. 包含和存在
-- nickname 为 gs 的用户 这里使用 ->> 查出的数据为text，所以匹配项也应该是text
select '{"nickname": "gs", "avatar": "avatar_url", "tags": ["python", "golang", "db"]}'::json->>'nickname' = 'gs';
select '{"nickname": "gs", "avatar": "avatar_url", "tags": ["python", "golang", "db"]}'::jsonb->>'nickname' = 'gs';
-- 使用 -> 查询，会抛出错误，这里无论匹配项是text类型的 'gs' 还是 json 类型的 '"gs"'::json都会抛出异常，json 类型不支持 等号（=）操作符
select '{"nickname": "gs", "avatar": "avatar_url", "tags": ["python", "golang", "db"]}'::json->'nickname' = '"gs"';
ERROR: operator does not exist: json = unknown
select '{"nickname": "gs", "avatar": "avatar_url", "tags": ["python", "golang", "db"]}'::json->'nickname' = '"gs"'::json;
ERROR: operator does not exist: json = json
-- jsonb 格式是可以查询成功的，这里使用 -> 查出的数据为json 对象，所以匹配项也应该是json 对象
select '{"nickname": "gs", "avatar": "avatar_url", "tags": ["python", "golang", "db"]}'::jsonb->'nickname' = '"gs"';
3. #> 和 #>> 操作符(数组)
使用 #>> 查出的数据为text 使用 #> 查出的数据为json 对象
select '{"nickname": "gs", "avatar": "avatar_url", "tags": ["python", "golang", "db"]}'::json#>'{tags,0}' as tag;
tag
----------
"python"
select '{"nickname": "gs", "avatar": "avatar_url", "tags": ["python", "golang", "db"]}'::json#>>'{tags,0}' as tag;
tag
--------
python
select '{"nickname": "gs", "avatar": "avatar_url", "tags": ["python", "golang", "db"]}'::jsonb#>'{tags,0}' = '"python"';
?column?
----------
t
select '{"nickname": "gs", "avatar": "avatar_url", "tags": ["python", "golang", "db"]}'::jsonb#>>'{tags,0}' = 'python';
?column?
----------
t
select '{"nickname": "gs", "avatar": "avatar_url", "tags": ["python", "golang", "db"]}'::json#>>'{tags,0}' = 'python';
?column?
----------
t
-- 会抛出错误，这里无论匹配项是text类型的 'python' 还是 json 类型的 '"python"'::json都会抛出异常，json 类型不支持 等号（=）操作符
select '{"nickname": "gs", "avatar": "avatar_url", "tags": ["python", "golang", "db"]}'::json#>'{tags,0}' = '"python"';
ERROR: operator does not exist: json = unknown
4. jsonb 数据查询（不适用于json）
@>操作符
-- nickname 为 nickname 的用户
select '{"nickname": "gs", "avatar": "avatar_url", "tags": ["python", "golang", "db"]}'::jsonb @> '{"nickname": "gs"}'::jsonb;
-- 等同于以下查询
-- 这里使用 -> 查出的数据为json 对象，所以匹配项也应该是json 对象
select '{"nickname": "gs", "avatar": "avatar_url", "tags": ["python", "golang", "db"]}'::jsonb->'nickname' = '"gs"';
select '{"nickname": "gs", "avatar": "avatar_url", "tags": ["python", "golang", "db"]}'::jsonb->>'nickname' = 'gs';
-- 查询有 python 和 golang 标签的数据
select '{"nickname": "gs", "avatar": "avatar_url", "tags": ["python", "golang", "db"]}'::jsonb @> '{"tags": ["python", "golang"]}';
?column?
----------
t
5. ?操作符、?|操作符和?&操作符
-- 查询有 avatar 属性的用户
select '{"nickname": "gs", "avatar": "avatar_url", "tags": ["python", "golang", "db"]}'::jsonb ? 'avatar';
-- 查询有 avatar 属性 并且avatar 数据不为空的数据
select '{"nickname": "gs", "avatar": null, "tags": ["python", "golang", "db"]}'::jsonb->>'avatar' is not null;
-- 查询 有 avatar 或 tags 的数据
select '{"nickname": "gs", "tags": ["python", "golang", "db"]}'::jsonb ?| array['avatar', 'tags'];
?column?
----------
t
-- 查询 既有 avatar 又有 tags 的用户
select '{"nickname": "gs", "tags": ["python", "golang", "db"]}'::jsonb ?& array['avatar', 'tags'];
?column?
----------
f
-- 查询 tags 中包含 python 标签的数据
select '{"nickname": "gs", "avatar": "avatar_url", "tags": ["python", "golang", "db"]}'::jsonb->'tags' ? 'python';
?column?
----------
t
