postgresql 日期类型处理实践

---- 日期+1 

select date '2018-01-14' + integer '1';

结果: 2018-01-15

---- 日期+1 后 转 20180101 日期字符串

select to_char(date '2018-01-14' + integer '1','yyyymmdd');

结果：20180114

---- 获取指定日期的周一日期

select to_char(date '2018-01-14' + integer '5' - (extract (dow from date '2018-01-14' + integer '5') - 1 || ' day')::interval,'yyyymmdd')

结果：20180115

--- 周"中文"
select case when extract (dow from date '2018-01-14' )=1 then '一'
when extract (dow from date '2018-01-14' )=2 then '二'
when extract (dow from date '2018-01-14' )=3 then '三'
when extract (dow from date '2018-01-14' )=4 then '四'
when extract (dow from date '2018-01-14' )=5 then '五'
when extract (dow from date '2018-01-14' )=6 then '六'
when extract (dow from date '2018-01-14' )=0 then '日'
else '' end

附使用函数生成一张日期数据表：

CREATE OR REPLACE FUNCTION "ext"."tmp_generate_date"()
  RETURNS "pg_catalog"."void" AS $BODY$
declare runstr text;
strdate date;
strweekid  text;
strweekstart text; -- 周开始第一天（周一）
strweek_zh text;
numb int :=1;
total int :=365;
begin



WHILE numb < total LOOP

    execute 'select date ''2018-01-14'' + integer '''||numb::text||''';' into strdate;

    execute 'select week_id from ext.tmp_weekmapping where '''||to_char(strdate,'yyyy-mm-dd')||''' >= to_char(week_start,''yyyy-mm-dd'') 
    and 
    '''||to_char(strdate,'yyyy-mm-dd')||''' <= to_char(week_end,''yyyy-mm-dd'')  limit 1;' into strweekid;

    execute 'select case when extract (dow from date '''||strdate||''')=1 then ''一''
    when extract (dow from date '''||strdate||''')=2 then ''二''
    when extract (dow from date '''||strdate||''')=3 then ''三''
    when extract (dow from date '''||strdate||''')=4 then ''四''
    when extract (dow from date '''||strdate||''')=5 then ''五''
    when extract (dow from date '''||strdate||''')=6 then ''六''
    when extract (dow from date '''||strdate||''')=0 then ''日''
    else '''' end' into strweek_zh;

    execute '
    select case when extract(dow from date '''||strdate||''')=0 then to_char(date '''||strdate||''' - integer ''6'',''yyyymmdd'')
    else to_char(date '''||strdate||''' - (extract (dow from date '''||strdate||''') - 1 || '' day'')::interval,''yyyymmdd'') end
    ' into strweekstart;
    

    insert into ext.tmp_date_tag(ir_date,ir_idx,ir_day,ir_weeka,ir_weekb,ir_month,ir_year,ir_weekday,ir_weekt)
    values(strdate,to_char(strdate,'yyyymmdd'),'D'||to_char(strdate,'yyyymmdd'),
    'W'||strweekstart,'W'||strweekid,'M'||to_char(strdate,'yyyymm'),'Y'||to_char(strdate,'yyyy'),strweek_zh,strweekstart);

    numb:=numb+1;

END LOOP;


end
$BODY$
  LANGUAGE 'plpgsql' VOLATILE COST 100
;

ALTER FUNCTION "ext"."tmp_generate_date"() OWNER TO "execute_role";

结果数据表：