如何在pgSQL中将日期作为整数获取

Question

How can I get the date period by months in pgsql. I need to divide a value by month range of the date period. Like 2016/01/31 and 2016/03/31 have 2 months of period and I need to divide a value by 2. I am sending two dates as parameters for the pgSQL stored procedure.

Answer 1

Here is Function Date Period by Months Period Return Integer

CREATE OR REPLACE FUNCTION date_period(start_date varchar,end_date varchar) RETURNS integer AS $LABEL$
DECLARE 
v_result integer;
BEGIN

select into v_result date_part('months',end_date::date)- date_part('months',start_date::date) as date_period;

/* v_result is integer */
RETURN (v_result);

/* COMMENTS THIS IF YOU IN DEBUG MODE */
EXCEPTION WHEN OTHERS THEN RETURN (999);

END;
$LABEL$ LANGUAGE plpgsql
SECURITY DEFINER;

EXEC: SELECT * FROM date_period('2016/01/31','2016/05/31');

FOR DIFFERENT YEAR YOU COULD USE THIS FUNCTION

CREATE OR REPLACE FUNCTION date_period(start_date varchar,end_date varchar) RETURNS integer AS $LABEL$
DECLARE 
v_result integer;
BEGIN

select into v_result ((extract( epoch from end_date::date)-extract( epoch from start_date::date))/60/60/24/30)::integer as date_period;

/* v_result is integer */
RETURN (v_result);

/* COMMENTS THIS IF YOU IN DEBUG MODE */
EXCEPTION WHEN OTHERS THEN RETURN (999);

END;
$LABEL$ LANGUAGE plpgsql
SECURITY DEFINER;

EXEC: SELECT * FROM date_period('2016/01/31','2017/03/31');