两种方法：
第一种：
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
|
DO $$declare d int;declare d1 varchar(100);declare d2 varchar(100);declare d3 date;declare d4 date;begind3:= CURRENT_DATE;d1:='select date'''|| d3 ||'''';d:=(SELECT EXTRACT(DOW FROM d3))-1;d2:=d1 || '-INTERVAL ''' || d || ' day '' ';EXECUTE d2 into d4;RAISE NOTICE 'ok %',d4;end$$ |
结果：
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
|
[SQL]DO $$declare d int;declare d1 varchar(100);declare d2 varchar(100);declare d3 date;declare d4 date;begind3:= CURRENT_DATE;d1:='select date'''|| d3 ||'''';d:=(SELECT EXTRACT(DOW FROM d3))-1;d2:=d1 || '-INTERVAL ''' || d || ' day '' ';EXECUTE d2 into d4;RAISE NOTICE 'ok %',d4;end$$NOTICE: ok 2016-06-13时间: 0.004s受影响的行: 0 |
解析：
declare ：声明变量
CURRENT_DATE ： 得到当前日期
SELECT CURRENT_DATE;
结果：
|
1
2
|
date2016-06-12 |
extract :从时间中抽出相应的字段
DOW 一周里的第几天 （sunday =0 saturday=6）
格式：
|
1
|
EXTRACT(field FROM source) |
当前日期是一周里面的第几天
|
1
|
SELECT EXTRACT(DOW FROM CURRENT_DATE); |
结果：
|
1
2
|
date_part 0 |
INTERVAL ：时间间隔类型
EXECUTE ：执行一个准备好的查询
RAISE NOTICE ：把结果显示出来
第二种：
|
1
|
SELECT CURRENT_DATE +cast(-1*(TO_NUMBER(to_char(CURRENT_DATE,'D'),'99')-2) ||' days' as interval); |
结果：
|
1
2
|
？column？2016-06-13 00:00:00 |
解析：
TO_NUMBER 将一个字符串转换成数字
格式：
|
1
|
TO_NUMBER(string,format) |
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
|
-- 一周里的日子(1-7；周日是1)select to_char(CURRENT_DATE ,'D')DDD 一年里的日子(001-366)DD 一个月里的日子(01-31)D 一周里的日子(1-7；周日是1)select to_char (to_date('2016-06-12','yyyy-mm-dd'),'D')select to_number(‘1.1','9.99') from dual;1.1select to_number(‘1.121','9.99') from dual;1.12-- 将得到的字符串转换成数字select TO_NUMBER(to_char(CURRENT_DATE,'D'),'99')-- 因为得到的星期一为2，所以要减去2select TO_NUMBER(to_char(CURRENT_DATE,'D'),'99')-2-- 将得到的数字乘以 -1 比如例子中：-1*3 就是 -3 ，也就是减去 3天select cast(-1*3 || 'days' as interval) -- 就是将当天减去0天 得到了星期一的日期select cast(-1*0 || 'days' as interval) + CURRENT_DATESELECT to_char(CURRENT_DATE +cast(-1*(TO_NUMBER(to_char(CURRENT_DATE,'D'),'99')-2) ||' days' as interval),'yyyy-mm-dd'); |
补充：Postgresql数据数据库中按日、月、周、年、时、分，30分钟的统计解决方案
对要统计的时间字段进行字符转换处理，再按照其分组即可实现对数据进行日，周，月，年，时，分，秒的统计
1、按日统计
|
1
2
|
to_char( h.row_date, 'yyyy-MM-dd' ) AS row_date2GROUP BY to_char( h.row_date, 'yyyy-MM-dd' ) |
2、按月统计
|
1
2
|
to_char(h.row_date, 'yyyy-MM' ) AS row_date2GROUP BY to_char(h.row_date, 'yyyy-MM' ) |
3、按年统计
|
1
2
|
to_char( h.row_date,'yyyy' ) AS row_date2GROUP BY to_char( h.row_date,'yyyy' ) |
4、按小时统计
|
1
2
|
to_char( h.row_date, 'yyyy-MM-dd HH' ) AS row_date2GROUP BY to_char( h.row_date, 'yyyy-MM-dd HH' ) |
5、按分钟统计
|
1
2
|
to_char( h.row_date, 'yyyy-MM-dd HH:mm' ) AS row_date2GROUP BY to_char( h.row_date, 'yyyy-MM-dd HH:mm' ) |
6、按周统计
按周统计最简单法
对时间row_date字段做处理，变成对应日期周一时间，然后按这个周一的时间去统计。减1的操作表示为对应日期的星期一，减1,2,3,4,5,6,7分别是对应日期的周一，周二，周三，周四，周五、周六、周日。
|
1
|
to_char( h.row_date-(extract (dow from h.row_date) - 1 ||'day')::interval,'yyyy-MM-dd') row_date |
然后按上面的语句分组统计即可实现按周统计，下面对应分组函数
|
1
|
GROUP BY to_char(h.row_date-(extract (dow from h.row_date) - 1 ||'day')::interval,'yyyy-MM-dd') |
按周统计之方法二（较复杂，不建议使用）
|
1
|
to_char(h.row_date, 'yyyy' ) || EXTRACT ( week FROM h.row_date ) :: INTEGER ASrow_date2 |
获取到数据库输出的字段中的年份和周数。
|
1
2
3
4
5
6
7
8
9
10
11
|
String row_date=rs.getString("row_date2");//获取数据库输出日期的年份 int year=Integer.parseInt(row_date.substring(0, 4));//获取数据库输出日期的周数 if(row_date.length()>=6){ week=Integer.parseInt(row_date.substring(4,6));} else{ week=Integer.parseInt(row_date.substring(4,5)); } String row_date2=getFirstDayOfWeek(year, week); trafficMap.put("row_date", row_date2); |
将查询出的内容日期转换成当周周一的时间
|
1
2
3
4
5
6
7
8
9
10
11
|
//将周统计中获取的如201636，表示2016年36周，获取其周一的时间 public String getFirstDayOfWeek(int year, int week) { // 先滚动到该年 nows.set(Calendar.YEAR, year); // 滚动到周 nows.set(Calendar.WEEK_OF_YEAR, week); // 得到该周第一天 nows.set(Calendar.DAY_OF_WEEK, 2); String firstDay = df.format(nows.getTime()); return firstDay; } |
7、按30分钟进行统计
|
1
2
|
case when substr( to_char(h.row_date, 'yyyy-mm-dd hh24:mi'),15, 16) :: integer <=30 then to_char(h.row_date, 'yyyy-mm-dd hh24')||':30' else to_char( h.row_date, 'yyyy-mm-ddhh24' )||':60' end as row_date2GROUP BY case when substr( to_char(h.row_date, 'yyyy-mm-dd hh24:mi'),15, 16) :: integer <=30 then to_char(h.row_date, 'yyyy-mm-dd hh24')||':30' else to_char( h.row_date, 'yyyy-mm-ddhh24' )||':60' end |
以上为个人经验，希望能给大家一个参考，也希望大家多多支持服务器之家。如有错误或未考虑完全的地方，望不吝赐教。
原文链接：https://blog.csdn.net/u011008029/article/details/51648193
