Advanced Search

datediff function in postgresql

/ Published in: SQL
Save to your folder(s)
A function which will give the difference between two dates in days, month or years.

Expand | Embed | Plain Text

Copy this code and paste it in your HTML 
  1. CREATE FUNCTION datediff (diffType CHARACTER VARYING(15), date1 DATE, date2 DATE) RETURNS INTEGER AS $$
  2. DECLARE
  3. 	YEAR_CONST CHARACTER VARYING(15) := 'year';
  4. 	MONTH_CONST CHARACTER VARYING(15) := 'month';
  5. 	DAY_CONST CHARACTER VARYING(15) := 'day';
  6.  
  7. 	diffInInterval INTERVAL;
  8. 	diffInDoublePrecision DOUBLE PRECISION := 0;
  9. 	diffInInteger INTEGER := 0;
  10. 	dateTemp DATE;
  11. BEGIN
  12.  
  13. 	diffInInterval := age(date2, date1);
  14.  
  15. 	IF LOWER($1) = LOWER(YEAR_CONST) THEN
  16. 		diffInDoublePrecision := date_part('Year', diffInInterval);
  17. 	ELSEIF LOWER($1) = LOWER(MONTH_CONST) THEN
  18. 		diffInDoublePrecision := (date_part('Year', diffInInterval) * 12) + date_part('Month', diffInInterval);
  19. 	ELSEIF LOWER($1) = LOWER(DAY_CONST) THEN
  20. 		diffInDoublePrecision := endDate - startDate;
  21. 	END IF;
  22.  
  23. 	diffInInteger := CAST(diffInDoublePrecision AS INTEGER);
  24. 	RETURN diffInInteger;
  25. END;
  26. $$ LANGUAGE plpgsql;

Report this snippet


Comments

RSS Icon Subscribe to comments

You need to login to post a comment.