None
Blog / 2010 / May / 4

Postgres Years Between

May 4, 2010

I wanted to do a SQL query which would show the earliest and latest dates for something, and show the number of years between them. This was to clean some data that linked people to events - if we had the same person taking part in events in both 1901 and 2001, it probably wasn't the same person!

SELECT extract(year from max(contests_contestevent.date_of_event)), 
      extract(year from min(contests_contestevent.date_of_event)), 
      conductors_conductor.name, 
      extract(year from max(contests_contestevent.date_of_event))
                 - extract(year from min(contests_contestevent.date_of_event)) as diff
FROM contests_contestresult, contests_contestevent, conductors_conductor
WHERE contests_contestresult.conductor_id = conductors_conductor.id
AND contests_contestresult.contest_event_id = contests_contestevent.id
GROUP BY conductors_conductor.name
ORDER BY diff desc

This selects four columns:

  • the year from the maximum date
  • the year from the minimum date
  • the person's name
  • the number of years between the minimum and maximum, calculated by taking the min column value from the max.

Tags: postgres years between extract