AGE function for differences in PostgreSQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

Calculate age difference between two dates

What is the output of this query in PostgreSQL?

SELECT AGE('2024-06-15', '2000-01-01');

PostgreSQL

SELECT AGE('2024-06-15', '2000-01-01');

A 24 years 6 mons 14 days

B 23 years 5 mons 14 days

C 24 years 5 mons 14 days

D 25 years 5 mons 14 days

💡 Hint

Remember AGE returns the interval between two dates as years, months, and days.

The AGE function calculates the difference between the first and second date. From 2000-01-01 to 2024-06-15 is 24 years, 5 months, and 14 days.

❓ query_result

intermediate

AGE function with current date

What will this query return if today is 2024-06-15?

SELECT AGE(CURRENT_DATE, '1990-12-31');

PostgreSQL

SELECT AGE(CURRENT_DATE, '1990-12-31');

A 33 years 5 mons 14 days

B 33 years 5 mons 15 days

C 34 years 5 mons 15 days

D 33 years 6 mons 15 days

💡 Hint

Calculate the difference from 1990-12-31 to 2024-06-15.

From 1990-12-31 to 2024-06-15 is 33 years, 5 months, and 15 days.

📝 Syntax

advanced

Identify the syntax error in AGE usage

Which option contains a syntax error when using the AGE function in PostgreSQL?

A SELECT AGE('2000-01-01', '2024-06-15');

B SELECT AGE('2024-06-15', '2000-01-01');

C SELECT AGE(TIMESTAMP '2024-06-15', TIMESTAMP '2000-01-01');

D SELECT AGE('2024-06-15');

💡 Hint

AGE requires either one or two arguments, but one argument must be a timestamp or date.

AGE with one argument returns the interval between that date and the current date. AGE with two arguments returns the interval between them. Option D is missing the second argument and uses a string literal without casting, causing a syntax error.

❓ optimization

advanced

Optimize AGE function usage for large datasets

You want to calculate the age difference between a column 'birth_date' and today for millions of rows efficiently. Which query is best optimized?

A SELECT EXTRACT(YEAR FROM AGE(CURRENT_DATE, birth_date)) FROM users;

B SELECT CURRENT_DATE - birth_date FROM users;

C SELECT AGE(CURRENT_DATE, birth_date) FROM users;

D SELECT DATE_PART('year', CURRENT_DATE) - DATE_PART('year', birth_date) FROM users;

💡 Hint

Consider which option calculates age in years correctly and efficiently.

Option A uses AGE and extracts the year part, giving accurate age in years. Option A returns interval which is less useful directly. Option A returns days difference, not age. Option A only subtracts years ignoring months and days, causing inaccuracies.

🧠 Conceptual

expert

Understanding AGE function output format

Which statement about the output of the AGE function in PostgreSQL is TRUE?

A AGE returns an interval showing years, months, and days, accounting for calendar differences.

B AGE always returns the difference as a simple integer number of days.

C AGE returns a string formatted as 'YYYY-MM-DD' representing the difference.

D AGE returns the difference in seconds as a floating-point number.

💡 Hint

Think about how AGE represents differences between dates.

AGE returns an interval type that shows the difference in years, months, and days, considering calendar variations like leap years and varying month lengths.