Posted By

arunpjohny on 09/07/07


Tagged

postgres


Versions (?)

Who likes this?

2 people have marked this snippet as a favorite

robe
letosword


datediff function in postgresql


 / Published in: SQL
 

A function which will give the difference between two dates in days, month or years.

  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  

You need to login to post a comment.