Return to Snippet

Revision: 4017
at October 16, 2007 22:40 by arunpjohny

Initial Code
CREATE OR REPLACE FUNCTION datediff (diffType Character Varying(15), date1 Date, date2 Date) RETURNS integer AS $$
	YEAR_CONST Character Varying(15) := 'year';
	MONTH_CONST Character Varying(15) := 'month';
	DAY_CONST Character Varying(15) := 'day';

	diffInInterval Interval;
	diffInDoublePrecision Double Precision := 0;
	diffInInteger Integer := 0;
	dateTemp Date;
	diffInInterval := age(date2, date1);

	IF lower($1) = lower(YEAR_CONST) THEN
		diffInDoublePrecision := date_part('Year', diffInInterval);
	ELSEIF lower($1) = lower(MONTH_CONST) THEN
		diffInDoublePrecision := (date_part('Year', diffInInterval) * 12) + date_part('Month', diffInInterval);
	ELSEIF lower($1) = lower(DAY_CONST) THEN
		diffInDoublePrecision := endDate - startDate;

	diffInInteger := CAST(diffInDoublePrecision as Integer);
	RETURN diffInInteger;
$$ LANGUAGE plpgsql;

Initial URL

Initial Description
Getting the difference between two dates in Postgresql. The difference can be in days, months or years.

For difference in days, months or years pass day, month or year as the first argument

Initial Title
Difference between two dates in Postgresql

Initial Tags

Initial Language