date difference in postgresql I have dates in 2 tables. Table student is as below:


ID|Name|Examdate
1  John 01/10/2015
2  Fred 11/12/2014


Table retake as below:


ID|RetakeDate
1  02/10/2016
2  05/11/2015


The IDs in retake relate to the students' IDs in student, i.e. they are the same.

I am trying to create another table where only students who have taken a retake after 12 months or more exist. The difference between examdate and RetakeDate needs to be >= 12months.

How do I achieve this?

There could be multiple rows in both tables for the same student. There will be multiple rows on both tables, as this will allow me to see how many times they have taken a retake and how many times an exam. At this moment in time I don't want to see if the retake is for the latest exam. I just want to see how many retakes they have taken all together.
End result should be comparing each Exam date with Retake date. Some more explanation:


  Q: So  if a student has dates in 2012, 2013, 2014 and then 2 retakes in 2016? 
  
  A: There should 3 rows in the result (one for each date)
  
  Q: What if it's three exams – in 2012, 2013, 2014 – and (a) two retakes – in 2014 and 2016? or (b) one retake – in 2014? What should be expected as the result in each of those cases?    
  
  A: It doesn't matter. As long as the (a) retake was taken after 12 months or more, they should appear in the result.
This query will return the times a student retook an exam after more than 12 months from the first take:

SELECT Name, COUNT(Name)
FROM student, retake
WHERE student.ID = retake.ID
AND RetakeDate::date - '12 months'::interval &gt;= Examdate::date
GROUP BY Name


It subtracts 12 months from RetakeDate and checks if the resulting date is greater or equal to Examdate.

This query works too:

SELECT Name, COUNT(Name)
FROM student, retake
WHERE student.ID = retake.ID
AND age(RetakeDate::date, Examdate::date) &gt;= '12 months'::interval
GROUP BY Name


The function age(timestamp, timestamp) is documented here: https://www.postgresql.org/docs/current/static/functions-datetime.html

ATTENTION

Be aware that those two queries can give different results for periods that are exactly 12 months. The following returns true:

select age('2000-12-31'::date, '2000-01-01'::date) = '12 months'::interval;

 ?column? 
----------
 t
(1 row)


while this query returns false:

select '2000-12-31'::date - '12 months'::interval = '2000-01-01'::date;

 ?column? 
----------
 f
(1 row)


In fact:

select '2000-12-31'::date - '12 months'::interval;
      ?column?       
---------------------
 1999-12-31 00:00:00
(1 row)


while:

select age('2000-12-31'::date, '2000-01-01'::date);
       age       
-----------------
 11 mons 30 days
(1 row)


It is your choice to choose age() over date - interval. To solve this, you can use &gt; against &gt;=.