CODEWITHSUNDEEP
phppostgresqldatestored-procedures
i am batman
i am batman

Reputation: 669

How to get the date period by months in pgSQL as an integer

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.

Upvotes: 0

Views: 80

Answers (1)

Adrian Hartanto
Adrian Hartanto

Reputation: 465

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');

Upvotes: 1

Related Questions