How to retrieve date in SQL?

Data and Databases
1

I encountered an issue while trying to execute the following SQL command:

EXTRACT('year' FROM (MIN(child.birth_date) - father.birth_date)) AS age

It results in an error message that states:

ERROR: function pg_catalog.extract(unknown, integer) does not exist

Could someone please guide me on how to fix this issue?

Thank you!

2

Hey, try casting the expression to an interval like this: age = extract(year from (MIN(child.birth_date) - father.birth_date)::interval). Casting it properly might solve the error. Sometimes the dat types need specification so the functions work perfect.

3

When dealing with date differences in SQL and looking to extract specific parts such as the year, ensure that you are working with a data type that supports interval operations. Instead of using the extract function on an integer, you may benefit from first calculating the age difference directly in years using a separate select statement like:

SELECT MIN(EXTRACT(YEAR FROM age(timestamp 'epoch' + (MIN(child.birth_date) - father.birth_date) * interval '1 day')))

This approach converts days into a timestamp which can then be converted back into years, thus bypassing the integer type error.