[postgresql] How to calculate DATE Difference in PostgreSQL?

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?

a simple way would be to cast the dates into timestamps and take their difference and then extract the DAY part.

if you want real difference

select extract(day from 'DATE_A'::timestamp - 'DATE_B':timestamp);

if you want absolute difference

select abs(extract(day from 'DATE_A'::timestamp - 'DATE_B':timestamp));

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
$$;

This is how I usually do it. A simple number of days perspective of B minus A.

DATE_PART('day', MAX(joindate) - MIN(joindate)) as date_diff