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.

Practice

1. What does the PostgreSQL AGE function return when given two timestamps?

easy

A. The number of seconds between the two timestamps as an integer

B. The difference between the two timestamps as an interval showing years, months, and days

C. The sum of the two timestamps as a new timestamp

D. The earlier of the two timestamps

Solution

1. Step 1: Understand the AGE function purpose

   The AGE function calculates the time difference between two timestamps.

2. Step 2: Identify the output format

   It returns the difference as an interval showing years, months, and days.

3. Final Answer:

   The difference between the two timestamps as an interval showing years, months, and days -> Option B

4. Quick Check:

   AGE returns interval difference [OK]

Hint: AGE shows difference as years, months, days interval [OK]

Common Mistakes:

• Thinking AGE returns seconds or integer
• Confusing AGE with addition or subtraction of timestamps
• Assuming AGE returns a timestamp

2. Which of the following is the correct syntax to get the age difference between two timestamps '2023-01-01' and '2024-06-01' in PostgreSQL?

easy

A. SELECT AGE('2024-06-01', '2023-01-01');

B. SELECT AGE('2023-01-01', '2024-06-01');

C. SELECT AGE('2024-06-01'::timestamp '2023-01-01'::timestamp);

D. SELECT AGE('2023-01-01'::timestamp, '2024-06-01'::timestamp);

Solution

1. Step 1: Check AGE function parameter order

   AGE calculates the difference as first timestamp minus second timestamp, so the later date should come first.

2. Step 2: Identify correct syntax with proper order

   Using AGE('2024-06-01', '2023-01-01') returns the positive interval difference.

3. Final Answer:

   SELECT AGE('2024-06-01', '2023-01-01'); -> Option A

4. Quick Check:

   Later date first in AGE [OK]

Hint: Put later date first in AGE function [OK]

Common Mistakes:

• Reversing the order of timestamps
• Not casting strings to timestamps when needed
• Using AGE with only one parameter incorrectly

3. What is the output of this query?

SELECT AGE('2024-06-01'::date, '2023-01-01'::date);

medium

A. 1 year 5 mons 1 day

B. 1 year 6 mons 0 days

C. 1 year 4 mons 30 days

D. 1 year 5 mons 0 days

Solution

1. Step 1: Calculate difference between dates

   From 2023-01-01 to 2024-06-01 is 1 year and 5 months exactly.

2. Step 2: Understand AGE output format

   AGE returns interval as years, months, and days; days are zero here.

3. Final Answer:

   1 year 5 mons 0 days -> Option D

4. Quick Check:

   AGE returns 1 year 5 mons 0 days [OK]

Hint: Count full years and months between dates [OK]

Common Mistakes:

• Counting months incorrectly as 6 instead of 5
• Confusing days count
• Assuming AGE returns total days only

4. Identify the error in this query:

SELECT AGE('2023-01-01');

medium

A. Incorrect date format used

B. AGE function cannot accept a single argument

C. No error; AGE accepts a single argument

D. AGE requires casting to timestamp explicitly

Solution

1. Step 1: Check AGE function argument requirements

   AGE can accept one or two arguments; with one argument it returns difference from current date.

2. Step 2: Analyze the query

   The query uses one argument but does not cast it; however, AGE accepts one argument as timestamp or date.

3. Step 3: Identify actual error

   The query is valid; PostgreSQL implicitly handles the date string and computes age to current timestamp. No error.

4. Final Answer:

   No error; AGE accepts a single argument -> Option C

5. Quick Check:

   AGE needs two timestamps or one with current date [OK]

Hint: AGE with one argument uses current date [OK]

Common Mistakes:

• Assuming AGE always needs two arguments
• Thinking date format is wrong
• Believing casting is mandatory

5. You want to find the exact age difference between a person's birthdate '1990-04-15' and today, but only in years ignoring months and days. Which query correctly uses AGE and extracts only the years part?

hard

A. SELECT EXTRACT(YEAR FROM AGE(CURRENT_DATE, '1990-04-15'));

B. SELECT EXTRACT(YEAR FROM AGE('1990-04-15', CURRENT_DATE));

C. SELECT AGE(CURRENT_DATE, '1990-04-15')::year;

D. SELECT DATE_PART('year', AGE('1990-04-15', CURRENT_DATE));

Solution

1. Step 1: Use AGE with current date and birthdate

   AGE(CURRENT_DATE, '1990-04-15') returns interval from birthdate to today.

2. Step 2: Extract only the year part

   Use EXTRACT(YEAR FROM interval) to get full years ignoring months and days.

3. Final Answer:

   SELECT EXTRACT(YEAR FROM AGE(CURRENT_DATE, '1990-04-15')); -> Option A

4. Quick Check:

   EXTRACT YEAR from AGE with later date first [OK]

Hint: Use EXTRACT(YEAR FROM AGE(later, earlier)) [OK]

Common Mistakes:

• Reversing AGE argument order
• Using wrong function like DATE_PART with reversed arguments
• Trying to cast interval directly to year