I try to convert an interval like "29 years 6 mons 22 days 21:17:52.860842" into a decimal year reprensentation like 29,55 years in PostgreSQL.

  • how would you define the length of a year exactly? Oct 27, 2019 at 22:24
  • That is exactly my problem. I could take the days and divide them through 365, but this does not include leap years. Is there some build in function for this?
    – Richard
    Oct 31, 2019 at 16:20

1 Answer 1

SELECT EXTRACT(EPOCH FROM INTERVAL '29 years 6 mons 22 days 21:17:52.860842')/31557600;

where 31557600 is roughly the number of seconds in a year.

  • You might want to ROUND that - I'm not sure anybody's interested in people's age to the nanosecond! :-)
    – Vérace
    Oct 28, 2019 at 5:34
  • From here, that should be 31556952 seconds! :-)
    – Vérace
    Oct 28, 2019 at 12:25
  • But I second the value that @Verace mentions here. Why use an approximate number when we know exactly how many seconds are in a year! Mar 29, 2021 at 20:15
  • There are leap days and leap seconds on some years. So you can't know how many seconds there are in "one year". You can only have an approximation or you can calculate each year (in history) separately. Leap seconds are usually known only about six months in advance. I gave one approximation, you can pick another. And on ROUND, a decimal value was asked, and given :) Apr 11, 2021 at 17:41

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.