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 0

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