PostgreSQL - DATEDIFF - Datetime Difference in Seconds, Days, Months, Weeks etc

You can use various datetime expressions or a user-defined DATEDIFF function (UDF) to calculate the difference between 2 datetime values in seconds, minutes, hours, days, weeks, months and years in PostgreSQL.

Overview

PostgreSQL does not provide DATEDIFF function similar to SQL Server DATEDIFF, but you can use various expressions or UDF to get the same results.

	SQL Server and Sybase	Postgresql
Years	DATEDIFF(yy, start, end)	DATE_PART('year', end) - DATE_PART('year', start)
Months	DATEDIFF(mm, start, end)	years_diff * 12 + (DATE_PART('month', end) - DATE_PART('month', start))
Days	DATEDIFF(dd, start, end)	DATE_PART('day', end - start)
Weeks	DATEDIFF(wk, start, end)	TRUNC(DATE_PART('day', end - start)/7)
Hours	DATEDIFF(hh, start, end)	days_diff * 24 + DATE_PART('hour', end - start )
Minutes	DATEDIFF(mi, start, end)	hours_diff * 60 + DATE_PART('minute', end - start )
Seconds	DATEDIFF(ss, start, end)	minutes_diff * 60 + DATE_PART('minute', end - start )

Version: PostgreSQL 9.1

PostgreSQL - Date Difference in Years

Consider SQL Server function to calculate the difference between 2 dates in years:

SQL Server:

-- Difference between Oct 02, 2011 and Jan 01, 2012 in years

SELECT DATEDIFF(year, '2011-10-02', '2012-01-01');

-- Result: 1

 

Note that SQL Server DATEDIFF function returned 1 year although there are only 3 months between dates.

SQL Server does not count full years passed between the dates, it calculates the difference between the year parts only.

In PostgreSQL, you can get the year parts from the dates and subtract them.

PostgreSQL:

-- Difference between Oct 02, 2011 and Jan 01, 2012 in years

SELECT DATE_PART('year', '2012-01-01'::date) - DATE_PART('year', '2011-10-02'::date);

-- Result: 1

 

PostgreSQL - Date Difference in Months

Consider SQL Server function to calculate the difference between 2 dates in months:

SQL Server:

-- Difference between Oct 02, 2011 and Jan 01, 2012 in months

SELECT DATEDIFF(month, '2011-10-02', '2012-01-01');

-- Result: 3

 

In PostgreSQL, you can take the difference in years, multiply by 12 and add the difference between month parts that can be negative.

PostgreSQL:

-- Difference between Oct 02, 2011 and Jan 01, 2012 in months

SELECT (DATE_PART('year', '2012-01-01'::date) - DATE_PART('year', '2011-10-02'::date)) 
 * 12 + (DATE_PART('month', '2012-01-01'::date) - DATE_PART('month', '2011-10-02'::date));
 
 -- Result: 3

 

PostgreSQL - Date Difference in Days

Consider SQL Server function to calculate the difference between 2 dates in days:

SQL Server:

-- Difference between Dec 29, 2011 23:00 and Dec 31, 2011 01:00 in days

SELECT DATEDIFF(day, '2011-12-29 23:00:00', '2011-12-31 01:00:00');

-- Result: 2

 

Note that DATEDIFF returned 2 days, although there is only 1 day and 2 hours between the datetime values.

 

In PostgreSQL, if you subtract one datetime value (TIMESTAMP, DATE or TIME data type) from another, you will get an INTERVAL value in the form ”ddd days hh:mi:ss”.

SELECT '2011-12-31 01:00:00'::timestamp - '2011-12-29 23:00:00'::timestamp; 

-- Result: "1 day 02:00:00"   


SELECT '2011-12-31 01:00:00'::timestamp - '2010-09-17 23:00:00'::timestamp;

-- Result: "469 days 02:00:00"

So you can use DATE_PART function to extact the number of days, but it returns the number of full days between the dates.

PostgreSQL:

-- Difference between Dec 29, 2011 23:00 and Dec 31, 2011 01:00 in days SELECT DATE_PART('day', '2011-12-31 01:00:00'::timestamp - '2011-12-29 23:00:00'::timestamp); -- Result: 1

PostgreSQL - Date Difference in Weeks

Consider SQL Server function to calculate the difference between 2 dates in weeks:

SQL Server:

-- Difference between Dec 22, 2011 and Dec 31, 2011 in weeks

SELECT DATEDIFF(week, '2011-12-22', '2011-12-31');

-- Result: 1

DATEDIFF returnes the number of full weeks between the datetime values.

In PostgreSQL, you can use an expression to define the number of days (see above) and divide it by 7. TRUNC is required to remove the decimal part after the division.

PostgreSQL:

-- Difference between Dec 22, 2011 and Dec 31, 2011 in weeks

SELECT TRUNC(DATE_PART('day', '2011-12-31'::timestamp - '2011-12-22'::timestamp)/7);

-- Result: 1

PostgreSQL - Datetime Difference in Hours

Consider SQL Server function to calculate the difference between 2 datetime value in hours:

SQL Server:

-- Difference between Dec 30, 2011 08:55 and Dec 30, 2011 9:05 in weeks

SELECT DATEDIFF(hour, '2011-12-30 08:55', '2011-12-30 09:05');

-- Result: 1

Note that DATEDIFF returned 1 hour although there is just 10 minutes difference between the datetime values.

In PostgreSQL, you can use an expression to define the number of days (see above), multiple by 24 and add the difference is hours.

PostgreSQL:

-- Difference between Dec 30, 2011 08:55 and Dec 30, 2011 9:05 in weeks

SELECT DATE_PART('day', '2011-12-30 08:55'::timestamp - '2011-12-30 09:05'::timestamp) * 24 
	+ DATE_PART('hour', '2011-12-30 08:55'::timestamp - '2011-12-30 09:05'::timestamp);

-- Result: 0

Note that this PostreSQL expression returns the number of full hours passed between the datetime values.

PostgreSQL - Datetime Difference in Minutes

Consider SQL Server function to calculate the difference between 2 datetime values in minutes:

SQL Server:

-- Difference between Dec 30, 2011 08:54:55 and Dec 30, 2011 08:56:10 in minutes

SELECT DATEDIFF(minute, '2011-12-30 08:54:55', '2011-12-30 08:56:10'); 

-- Result: 2   

-- Time only 
SELECT DATEDIFF(minute, '08:54:55', '08:56:10');

-- Result: 2

Note that DATEDIFF returned 2 minutes although there is just 1 minute and 15 seconds between the datetime values.

In PostgreSQL, you can use an expression to define the number of hours (see above), multiple by 60 and add the difference is minutes.

PostgreSQL:

-- Difference between Dec 30, 2011 08:54:55 and Dec 30, 2011 08:56:10 in minutes

SELECT (DATE_PART('day', '2011-12-30 08:56:10'::timestamp - '2011-12-30 08:54:55'::timestamp) * 24 
	+ DATE_PART('hour', '2011-12-30 08:56:10'::timestamp - '2011-12-30 08:54:55'::timestamp)) * 60 
    + DATE_PART('minute', '2011-12-30 08:56:10'::timestamp - '2011-12-30 08:54:55'::timestamp); 
    
    -- Result: 1   
    
    -- Time only 
    SELECT DATE_PART('hour', '08:56:10'::time - '08:54:55'::time) * 60 
    	+ DATE_PART('minute', '08:56:10'::time - '08:54:55'::time);

-- Result: 1

Note that these PostreSQL expressions return the number of full minutes passed between the datetime values.

PostgreSQL - Datetime Difference in Seconds

Consider SQL Server function to calculate the difference between 2 datetime values in seconds:

SQL Server:

-- Difference between Dec 30, 2011 08:54:55 and Dec 30, 2011 08:56:10 in seconds

SELECT DATEDIFF(second, '2011-12-30 08:54:55', '2011-12-30 08:56:10');

-- Result: 75  

-- Time only
SELECT DATEDIFF(second, '08:54:55', '08:56:10');

-- Result: 75

In PostgreSQL, you can use an expression to define the number of minutes (see above), multiple by 60 and add the difference is seconds.

PostgreSQL:

-- Difference between Dec 30, 2011 08:54:55 and Dec 30, 2011 08:56:10 in seconds

SELECT ((DATE_PART('day', '2011-12-30 08:56:10'::timestamp - '2011-12-30 08:54:55'::timestamp) * 24 
  + DATE_PART('hour', '2011-12-30 08:56:10'::timestamp - '2011-12-30 08:54:55'::timestamp)) * 60 
  + DATE_PART('minute', '2011-12-30 08:56:10'::timestamp - '2011-12-30 08:54:55'::timestamp)) * 60 
  + DATE_PART('second', '2011-12-30 08:56:10'::timestamp - '2011-12-30 08:54:55'::timestamp); 
  
  -- Result: 75   
  
  -- Time only 
  SELECT (DATE_PART('hour', '08:56:10'::time - '08:54:55'::time) * 60 
  	+ DATE_PART('minute', '08:56:10'::time - '08:54:55'::time)) * 60 
    + DATE_PART('second', '08:56:10'::time - '08:54:55'::time); 
    
    -- Result: 75

PostgreSQL DATEDIFF - User-Defined Function (UDF)

Besides a separate expression to calculate the datetime difference for each time unit, you can use a function similar to SQLServer DATEDIFF function.

PostgreSQL:

CREATE OR REPLACE FUNCTION DateDiff (units VARCHAR(30), start_t TIMESTAMP, end_t TIMESTAMP) 
RETURNS INT AS $$ 
DECLARE 
	diff_interval INTERVAL; 
    diff INT = 0; 
    years_diff INT = 0; 
BEGIN 
	IF units IN ('yy', 'yyyy', 'year', 'mm', 'm', 'month') THEN 
    	years_diff = DATE_PART('year', end_t) - DATE_PART('year', start_t);   
        
        IF units IN ('yy', 'yyyy', 'year') THEN 
        	-- SQL Server does not count full years passed (only difference between year parts) 
            RETURN years_diff; 
        ELSE 
        	-- If end month is less than start month it will subtracted 
            RETURN years_diff * 12 + (DATE_PART('month', end_t) - DATE_PART('month', start_t)); 
        END IF; 
     END IF;   
     
  	-- Minus operator returns interval 'DDD days HH:MI:SS' 
    diff_interval = end_t - start_t;   
    
    diff = diff + DATE_PART('day', diff_interval);   
    
    IF units IN ('wk', 'ww', 'week') THEN 
    	diff = diff/7; 
        RETURN diff; 
    END IF;   
    
    IF units IN ('dd', 'd', 'day') THEN 
    	RETURN diff; 
    END IF;   
    
    diff = diff * 24 + DATE_PART('hour', diff_interval);   
    
    IF units IN ('hh', 'hour') THEN 
    	RETURN diff; 
    END IF;   
    
    diff = diff * 60 + DATE_PART('minute', diff_interval);   
    
    IF units IN ('mi', 'n', 'minute') THEN 
    	RETURN diff; 
    END IF;   
    
    diff = diff * 60 + DATE_PART('second', diff_interval);   
    
    RETURN diff; 
END; 
$$ LANGUAGE plpgsql;

How to Use PostgreSQL DATEDIFF Function

The syntax is similar to SQL Server DATEDIFF, but you have to specify a time unit (second, minute etc. and their abbreviations) as a string literal in PostgreSQL, for example:

-- Difference between Dec 30, 2011 08:54:55 and Dec 30, 2011 08:56:10 in seconds
SELECT DATEDIFF('second', '2011-12-30 08:54:55'::timestamp, '2011-12-30 08:56:10'::timestamp);

-- Result: 75

PostgreSQL DATEDIFF Function for TIME Only

You can have another function that operates on time data types only. PostgreSQL supports overloaded functions having the same name, but different data types of parameters:

CREATE OR REPLACE FUNCTION DateDiff (units VARCHAR(30), start_t TIME, end_t TIME) 
RETURNS INT AS $$ 
DECLARE 
	diff_interval INTERVAL; 
    diff INT = 0; 
BEGIN 
	-- Minus operator for TIME returns interval 'HH:MI:SS' 
    diff_interval = end_t - start_t;   
    diff = DATE_PART('hour', diff_interval);   
    
    IF units IN ('hh', 'hour') THEN 
    	RETURN diff; 
    END IF;   
    
    diff = diff * 60 + DATE_PART('minute', diff_interval);   
    
    IF units IN ('mi', 'n', 'minute') THEN 
    	RETURN diff; 
    END IF;   
    
    diff = diff * 60 + DATE_PART('second', diff_interval);   
    
    RETURN diff; 
END; 
$$ LANGUAGE plpgsql;

 

For example, you can call this function as:

-- Difference between 08:54:55 and 08:56:10 in seconds
SELECT DATEDIFF('second', '08:54:55'::time, '08:56:10'::time);

-- Result: 75

출처 : http://www.sqlines.com/postgresql/how-to/datediff