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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

[postgresql] How to calculate DATE Difference in PostgreSQL?

Examples related to postgresql

Examples related to datediff