"Subtotal" PostgreSQL implements Oracle's trunc date function function

Create or replace function trunc (P_timestamp timestamp with time zone,  p_formart varchar default  ' DD ')  returns timestamp without time  zone as$ $declare  v_timestamp timestamp := null; v_formart varchar (Ten)   : = upper (P_formart);begin /* *  function function: Format the date value  *  parameter description:  *   P_ timestamp  (  Required Date value for format session  )  *   P_FORMART   ( yyyy: First day of the year;  mm| month| MON|RM: The first day of the month;   null| DD: the day;  d: The first day of the current week;     *  return Format: Yyyy-mm-dd hh24 (:mi:ss ) (The specified value is determined by the second parameter)  */ if p_timestamp is  not null then  if v_formart in  (' YYYY ',  ' year ')  then    --  the first day of the current year (yyyy-01-01 00:00:00)    v_timestamp := date_trunc (' Year ',  p_timestamp);   elsif v_formart in  (' month ',  ' MON ',  ' MM ',  ' RM ')  then   --  the first day of the current month ( yyyy-mm-01 00:00:00)    v_timestamp := date_trunc (' month ',  p_timestamp);   elsif v_formart in  (' DD ',  ' Day ',  ' DY ')  then   --   (yyyy-mm-dd 00:00:00)    v_timestamp := date_trunc (' Day ',  p_ Timestamp);  elsif v_formart =  ' D '  then   --  current week first day [Sunday for first day] ( yyyy-mm-dd 00:00:00)    v_timestamp :=  (Date_trunc (' WEEK ',  p_timestamp)  - interval ' 1 day ');  elsif v_formart in  (' W1 ',  ' W2 ',  ' W3 ',   ' W4 ',  ' W5 ',  ' W6 ',  ' W7 ')  then   --  current in days [Sunday for First day] (YYYY-MM-DD  00:00:00)    v_timestamp := date_trunc (' WEEK ',  p_timestamp)::d ate +  substr (v_formart, 2, 1):: Integer - 2;  elsif v_formart ~  ' ^d\+? [0-9]*$ '  then   --  the Day of the Year (yyyy-mm-dd 00:00:00)    if substr (v_ Formart, 2, length (V_formart)-1)::integer between 1 and 366 then     v_timestamp := date_trunc (' year ',  p_timestamp)::d ate + substr (v_ Formart, 2, length (V_formart)-1):: Integer - 1;    if date_trunc (' Year ',  v_timestamp)::d ate > date_trunc (' year ',  p_timestamp)::d ate then      v_timestamp := date_trunc (' year ',  v_timestamp)::d ate - interval ' 1  day ';    end if;   else    raise  exception  ' u-2001 [%] is not recognize. please enter  "D[1~366]" ',  p_formart;   end if;  elsif v_formart in  (' HH ',  ' HH24 ')  then   v_timestamp := date_trunc (' Hour ',  p_timestamp);  elsif v_formart =  ' HH12 '  then   v_timestamp  := to_char (p_timestamp,  ' yyyy-mm-dd hh12:00:00 '):: timestamp;  elsif v_ formart in  (' MINUTE ',  ' MI ')  then   v_timestamp := date_trunc (' Minute ',  p_timestamp);  elsif v_formart =  ' CC '  then   v_ Timestamp := to_date (Trunc (Date_part (' Years ',  p_timestamp):: integer/100) *100+1):: VarChar,   ' yyyy ');  elsif v_formart in  (' Help ',  '? ')  then   raise exception  ' U-2001 please enter formart code  in  ( yyyy| year, month| mon| mm|rm, dd| day| dy, d, w[1~7], d[1~366], hh| hh24, hh12, minute|mi, cc ) ';   else   raise exception  ' U-2001 [%] is not recognize. you  can try [help] ',  p_formart;  end if; else  v_timestamp  := p_timestamp; end if; return v_timestamp;end;$$ language plpgsql;  --  test Data Select trunc (current_date,  ' D360 '),  trunc (current_date,  ' D '), Trunc ( current_date,  ' W1 ');

This article is from the "accumulate Kuibu to thousands of Miles" blog, please be sure to keep this source http://chnjone.blog.51cto.com/4311366/1658843

"Subtotal" PostgreSQL implements Oracle's trunc date function function