Shulou(Shulou.com)06/01 Report--

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 / * * function: format the date value * Parameter description: * P_TIMESTAMP (date value that requires formatting) * P_FORMART (YYYY: the first day of the year; MM | MONTH | MON | RM: the first day of the month; NULL | DD: the same day D: the first day of the current week;....) * return format: YYYY-MM-DD HH24 (12): MI:SS (the specific 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 previous year (YYYY-01-01 00:00:00) v_timestamp: = date_trunc ('year', p_timestamp) Elsif v_formart in ('MONTH',' MON', 'MM',' RM') then-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-current day (YYYY-MM-DD 00:00:00) v_timestamp: = date_trunc ('day', p_timestamp) Elsif v_formart ='D' then-the first day of the current week [Sunday is the 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-the day of the current week [Sunday is the first day] (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-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):: 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' Umur2001 [%] is not recognize. Please enter "D [1mm 366]", paired form; 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 hh22:00:00'):: timest 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) * 100mm 1):: varchar, 'yyyy'); elsif v_formart in (' HELP','?') Then raise exception'Umuri 2001 please enter formart code in (YYYY | YEAR, MONTH | MON | MM | RM, DD | DAY | DY, D, W [1x 7], D [1x 366], HH | HH24, HH12, MINUTE | MI, CC)'; else raise exception'Umurl 2001 [%] is not recognize. You can try [help]', pairformart; end if; else v_timestamp: = paired timest end if; return vested timestamp political endgame $language plpgsql;-- test data select trunc (current_date, 'D360'), trunc (current_date,' D'), trunc (current_date, 'W1')

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.