postgresql 实现得到时间对应周的周一案例

两种方法:

第一种:

do $$
declare d int;
declare d1 varchar(100);
declare d2 varchar(100);
declare d3 date;
declare d4 date;
begin
d3:= 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$$

结果:

[sql]do $$
declare d int;
declare d1 varchar(100);
declare d2 varchar(100);
declare d3 date;
declare d4 date;
begin
d3:= 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;

结果:

date
2016-06-12

extract :从时间中抽出相应的字段

dow 一周里的第几天 (sunday =0 saturday=6)

格式:

extract(field from source)

当前日期是一周里面的第几天

select extract(dow from current_date);

结果:

date_part
    0

interval :时间间隔类型

execute :执行一个准备好的查询

raise notice :把结果显示出来

第二种:

select current_date +cast(-1*(to_number(to_char(current_date,'d'),'99')-2) ||' days' as interval);

结果:

?column?
2016-06-13 00:00:00

解析:

to_number 将一个字符串转换成数字

格式:

to_number(string,format) 
-- 一周里的日子(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.1
select to_number(‘1.121','9.99') from dual;
1.12
-- 将得到的字符串转换成数字
select to_number(to_char(current_date,'d'),'99')
-- 因为得到的星期一为2,所以要减去2
select 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_date
select  to_char(current_date +cast(-1*(to_number(to_char(current_date,'d'),'99')-2) ||' days' as interval),'yyyy-mm-dd');

补充:postgresql数据数据库中按日、月、周、年、时、分,30分钟的统计解决方案

对要统计的时间字段进行字符转换处理,再按照其分组即可实现对数据进行日,周,月,年,时,分,秒的统计

1、按日统计

to_char( h.row_date, 'yyyy-mm-dd' ) as row_date2
group by to_char( h.row_date, 'yyyy-mm-dd' )

2、按月统计

to_char(h.row_date, 'yyyy-mm' ) as row_date2
group by to_char(h.row_date, 'yyyy-mm' )

3、按年统计

to_char( h.row_date,'yyyy' ) as row_date2
group by to_char( h.row_date,'yyyy' )

4、按小时统计

to_char( h.row_date, 'yyyy-mm-dd hh' ) as row_date2
group by to_char( h.row_date, 'yyyy-mm-dd hh' )

5、按分钟统计

to_char( h.row_date, 'yyyy-mm-dd hh:mm' ) as row_date2
group by to_char( h.row_date, 'yyyy-mm-dd hh:mm' )

6、按周统计

按周统计最简单法

对时间row_date字段做处理,变成对应日期周一时间,然后按这个周一的时间去统计。减1的操作表示为对应日期的星期一,减1,2,3,4,5,6,7分别是对应日期的周一,周二,周三,周四,周五、周六、周日。

to_char( h.row_date-(extract (dow from h.row_date) - 1 ||'day')::interval,'yyyy-mm-dd') row_date

然后按上面的语句分组统计即可实现按周统计,下面对应分组函数

group by to_char(h.row_date-(extract (dow from h.row_date) - 1 ||'day')::interval,'yyyy-mm-dd')

按周统计之方法二(较复杂,不建议使用)

to_char(h.row_date, 'yyyy' ) || extract ( week from h.row_date ) :: integer asrow_date2

获取到数据库输出的字段中的年份和周数。

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);

将查询出的内容日期转换成当周周一的时间

//将周统计中获取的如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分钟进行统计

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_date2
group 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

以上为个人经验,希望能给大家一个参考,也希望大家多多支持www.887551.com。如有错误或未考虑完全的地方,望不吝赐教。

(0)
上一篇 2022年3月21日
下一篇 2022年3月21日

相关推荐