76

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?

Muhammad Waheed
  • 1,048
  • 1
  • 13
  • 30
Sarfaraz Makandar
  • 5,933
  • 16
  • 59
  • 84

6 Answers6

87

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.

Joachim Isaksson
  • 176,943
  • 25
  • 281
  • 294
  • 2
    @JurajPetrik Hm, `SELECT EXTRACT(DAY FROM TO_TIMESTAMP('2016-01-01', 'YYYY-MM-DD')-TO_TIMESTAMP('2015-03-01', 'YYYY-MM-DD'));` gives 306 days which would seem to be correct (and greater than 31). – Joachim Isaksson Dec 07 '16 at 12:10
22

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));
zubair-0
  • 988
  • 8
  • 15
  • 6
    The question is old, so maybe it didn't work back then, but now you can do `select date_x - date_y as thing` – coladict Mar 05 '20 at 11:16
  • 1
    One warning here. This only extracts the day component from the period. A) 3 days 23 hours would still result in 3, B) `extract(hour from ...)` 3 days and 23 hours will result in 23 and not 95 and C) the month and year component is always 0. – jakob-r Feb 17 '22 at 08:25
17

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
$$;
Frank Heikens
  • 117,544
  • 24
  • 142
  • 135
14

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
anoraq
  • 515
  • 7
  • 9
7

This is not a direct answer to OP's question, however, this is the answer I was looking for when my search brought me to this thread.

For someone who wants to create a human-readable string that denotes the difference between two dates, consider using AGE().

 AGE(table.end_date, table.start_date)

It produces results that looks like these:

0:00:27

3 days 22:06:39.833264
4 days 01:12:39.473559
20 days 17:53:20.23287
23 days 21:01:15.150703

1 mon 19 days 01:52:24.262275
2 mons 17 days 05:04:12.277099
2 mons 17 days 04:59:21.618069
Clint Gossett
  • 71
  • 1
  • 4
1

If you have a timestamp already you will need not to cast anything,

For the absolute difference @zubair-0 answer is great here is implementation an using...

select abs(extract(day from A - B)) from table_name;

Note: A and B are timestamps as data types

MUGABA
  • 751
  • 6
  • 7