Geek Answers Handbook

How to calculate date difference in PostgreSQL?

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

In SQL Server : as we do in SQL Server, this is a lot easier.

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

My attempt : I am trying to use the following script:

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

Question :

  • Is my method correct?

  • Is there any function in PostgreSQLto calculate this?

+14
source share
3 answers

Your calculations are true for types DATE, but if your values ​​are timestamps, you should probably use EXTRACT(or DATE_PART) remember to get only the difference in full days;

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

SQLfiddle . , 1 2 .

+21

CAST datatype DATE, :

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

:

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

: 31

lateiff():

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

This is how I usually do it. The simple number of days of the prospect is B minus A.

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

More articles:


All Articles