Postgresql implementation obtains the Monday case of the corresponding week

Two methods:

The first type:

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$$

result:

[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
time: 0.004s
Affected rows: 0

Analysis:

declare: declare variable

CURRENT_DATE : Get the current date

SELECT CURRENT_DATE;

result:

date
2016-06-12

extract: Extract the corresponding field from time

DOW What day of the week (sunday = 0 satisfaction=6)

Format:

EXTRACT(field FROM source)

What day of the week is the current date

SELECT EXTRACT(DOW FROM CURRENT_DATE);

result:

date_part
    0

INTERVAL: Time interval type

EXECUTE: execute a prepared query

RAISE NOTICE: Show the results

The second type:

SELECT CURRENT_DATE +cast(-1*(TO_NUMBER(to_char(CURRENT_DATE,'D'),'99')-2) ||' days' as interval);

result:

？column？
2016-06-13 00:00:00

Analysis:

TO_NUMBER Converts a string to a number

Format:

TO_NUMBER(string,format)

-- Days of the week(1-7；Sunday is1)
select to_char(CURRENT_DATE ,'D')
DDD Days of the year(001-366)
DD Days of a month(01-31)
D  Days of the week(1-7；Sunday is1)
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
-- Convert the resulting string to a number
select TO_NUMBER(to_char(CURRENT_DATE,'D'),'99')
-- Because the Monday is2，So subtract2
select TO_NUMBER(to_char(CURRENT_DATE,'D'),'99')-2
-- Multiply the resulting number by -1 For example, in the example：-1*3 that is -3 ，也that is减去 3sky
select cast(-1*3 || 'days' as interval) 
-- that is将当sky减去0sky Get the Monday date
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');

Supplement: 30-minute statistical solution in Postgresql data database

Convert the time fields to be counted and then grouped according to them to achieve the statistics of day, week, month, year, hour, minute and second.

1. Daily statistics

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

2. Monthly statistics

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

3. Yearly statistics

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

4. Statistics by hour

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

5. Statistics by minute

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. Weekly statistics

The easiest way to count by week

Process the time row_date field and become the corresponding date Monday time, and then count according to the Monday time. The operation of subtracting 1 is expressed as the Monday of the corresponding date, and minus 1, 2, 3, 4, 5, 6, 7 is the Monday, Tuesday, Wednesday, Thursday, Friday, Saturday, and Sunday of the corresponding date.

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

Then group statistics according to the above statement to achieve weekly statistics, the following corresponding grouping functions

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

Weekly statistics method 2 (more complicated and not recommended)

to_char(h.row_date, 'yyyy' ) || EXTRACT ( week FROM h.row_date ) :: INTEGER ASrow_date2

Gets the year and weeks in the fields output to the database.

String row_date=("row_date2");
//Get the year of the database output date int year=(row_date.substring(0, 4));
//Get the number of weeks of the database output date  if(row_date.length()&gt;=6){
         week=(row_date.substring(4,6));}
  else{
        week=(row_date.substring(4,5));
      }
      String row_date2=getFirstDayOfWeek(year, week);
      ("row_date", row_date2);

Convert the date of the query to the time of the Monday of the week

//The weekly statistics obtained, such as 201636, indicates the 36th week of 2016, and obtain the Monday time  public String getFirstDayOfWeek(int year, int week) {
    // Scroll to that year first     (, year);
    // Scroll to week     (Calendar.WEEK_OF_YEAR, week);
    // Get the first day of the week     (Calendar.DAY_OF_WEEK, 2);
    String firstDay = (());
    return firstDay;
    }

7. Statistics in 30 minutes

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

The above is personal experience. I hope you can give you a reference and I hope you can support me more. If there are any mistakes or no complete considerations, I would like to give you advice.