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

date
2016-06-12

EXTRACT(field FROM source)

SELECT EXTRACT(DOW FROM CURRENT_DATE);

date_part
    0

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

to_char( h.row_date, 'yyyy-MM-dd' ) AS row_date2
GROUP BY to_char( h.row_date, 'yyyy-MM-dd' )

to_char(h.row_date, 'yyyy-MM' ) AS row_date2
GROUP BY to_char(h.row_date, 'yyyy-MM' )

to_char( h.row_date,'yyyy' ) AS row_date2
GROUP BY to_char( h.row_date,'yyyy' )

to_char( h.row_date, 'yyyy-MM-dd HH' ) AS row_date2
GROUP BY to_char( h.row_date, 'yyyy-MM-dd HH' )

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

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

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