How to calculate DATE Difference in PostgreSQL?

Question

Here I need to calculate the difference of the two dates in the PostgreSQL.

In SQL Server: Like we do in SQL Server its much easier.

DATEDIFF(Day, MIN(joindate), MAX(joindate)) AS DateDifference; 

My Try: I am trying using the following script:

(Max(joindate) - Min(joindate)) as DateDifference; 

Question:

• Is my method correct?

• Is there any function in PostgreSQL to calculate this?

Answer 1

Your calculation is correct for DATE types, but if your values are timestamps, you should probably use EXTRACT (or DATE_PART) to be sure to get only the difference in full days;

EXTRACT(DAY FROM MAX(joindate)-MIN(joindate)) AS DateDifference 

An SQLfiddle to test with. Note the timestamp difference being 1 second less than 2 full days.

Answer 2

CAST both fields to datatype DATE and you can use a minus:

(CAST(MAX(joindate) AS date) - CAST(MIN(joindate) AS date)) as DateDifference 

Test case:

SELECT  (CAST(MAX(joindate) AS date) - CAST(MIN(joindate) AS date)) as DateDifference FROM      generate_series('2014-01-01'::timestamp, '2014-02-01'::timestamp, interval '1 hour') g(joindate); 

Result: 31

Or create a function datediff():

CREATE OR REPLACE FUNCTION datediff(timestamp, timestamp)  RETURNS int  LANGUAGE sql  AS $$     SELECT CAST($1 AS date) - CAST($2 AS date) as DateDifference $$;