【小计】PostgreSQL实现Oracle的trunc日期函数功能

wolong

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(10) := upper(p_formart);
　　
begin
　　
/*
　　
* 函数功能:对日期值进行格式化
　　
* 参数说明:
　　
*   P_TIMESTAMP ( 需要格式话的日期值 )
　　
*   P_FORMART  ( YYYY:年第一天; MM|MONTH|MON|RM:月第一天;  NULL|DD:当日; D:当前周第一天;  ....)
　　
* 返回格式:YYYY-MM-DD HH24(12):MI:SS (具体值由第二个参数决定)
　　
*/
　　
if p_timestamp is not null then
　　
  if v_formart in ('YYYY', 'YEAR') then
　　
   -- 当前年的第一天(YYYY-01-01 00:00:00)
　　
   v_timestamp := date_trunc('year', p_timestamp);
　　
  elsif v_formart in ('MONTH', 'MON', 'MM', 'RM') then
　　
   -- 当前月第一天(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
　　
   -- 当前周第一天[周日为第一天](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
　　
   -- 当前周第几天[周日为第一天](YYYY-MM-DD 00:00:00)
　　
   v_timestamp := date_trunc('WEEK', p_timestamp)::date + substr(v_formart, 2, 1)::integer - 2;
　　
  elsif v_formart ~ '^D\+?[0-9]*$' then
　　
   -- 当年第几天(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)::date + substr(v_formart, 2, length(v_formart)-1)::integer - 1;
　　
    if date_trunc('year', v_timestamp)::date > date_trunc('year', p_timestamp)::date then
　　
     v_timestamp := date_trunc('year', v_timestamp)::date - 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;
　　

　　

　　
-- 测试数据
　　
select trunc(current_date, 'D360'), trunc(current_date, 'D'),trunc(current_date, 'W1');