AGE function for differences in PostgreSQL - Deep Dive

Overview - AGE function for differences

What is it?

The AGE function in PostgreSQL calculates the difference between two dates or timestamps. It returns the result as an interval showing years, months, and days. This helps you understand how much time has passed between two points in time in a human-friendly way.

Why it matters

Without the AGE function, calculating the exact difference between dates would require complex manual calculations. AGE simplifies this by giving a clear, readable interval that accounts for varying month lengths and leap years. This is important for applications like age calculation, subscription durations, or event timelines.

Where it fits

Before learning AGE, you should understand basic date and time data types in PostgreSQL. After mastering AGE, you can explore interval arithmetic, date_trunc functions, and advanced time zone handling.

Mental Model

Core Idea

AGE calculates the exact elapsed time between two dates as a readable interval of years, months, and days.

Think of it like...

It's like measuring the age of a person by counting full years, then leftover months, then leftover days, instead of just counting total days.

┌───────────────┐
│   DATE 1      │
└──────┬────────┘
       │
       ▼
┌───────────────┐
│   DATE 2      │
└──────┬────────┘
       │
       ▼
┌─────────────────────────────┐
│ AGE FUNCTION COMPUTES       │
│ Years, Months, Days interval│
└─────────────────────────────┘

Build-Up - 7 Steps

1

Foundation

Understanding Date and Timestamp Types

Concept: Learn what date and timestamp data types represent in PostgreSQL.

PostgreSQL stores dates as calendar days and timestamps as precise moments including time. Dates have no time part, timestamps include hours, minutes, and seconds. Knowing these types helps you understand what AGE works with.

Result

You can distinguish between date-only and timestamp values in queries.

Understanding the data types is essential because AGE behaves differently depending on whether you use dates or timestamps.

2

Foundation

Basic Interval Type Introduction

Concept: Intervals represent durations of time like '2 years 3 months'.

PostgreSQL interval type stores a span of time, which can include years, months, days, hours, minutes, and seconds. AGE returns this type to show differences between dates.

Result

You recognize interval outputs like '1 year 2 mons 3 days'.

Knowing intervals helps you interpret AGE results correctly as durations, not just numbers.

3

Intermediate

Using AGE with Two Dates

🤔Before reading on: do you think AGE('2024-06-01', '2023-06-01') returns '365 days' or '1 year'? Commit to your answer.

Concept: AGE calculates the difference between two dates as years, months, and days, not just total days.

Example: SELECT AGE('2024-06-01', '2023-06-01'); returns '1 year'. AGE counts full years first, then leftover months and days.

Result

'1 year' interval is returned, not '365 days'.

Understanding AGE returns a human-readable interval, not just a raw day count, is key to using it properly.

4

Intermediate

Using AGE with Timestamps

🤔Before reading on: does AGE('2024-06-01 12:00', '2023-06-01 11:00') include hours in the result? Commit to your answer.

Concept: AGE can also calculate differences between timestamps, including time parts.

Example: SELECT AGE('2024-06-01 12:00', '2023-06-01 11:00'); returns '1 year 1 hour'. AGE includes hours, minutes, and seconds when timestamps are used.

Result

'1 year 1 hour' interval is returned.

Knowing AGE handles time parts in timestamps helps you get precise elapsed times.

5

Intermediate

Default AGE Usage with One Argument

Concept: AGE with one argument calculates difference from current date/time.

Example: SELECT AGE('2000-01-01'); returns interval between now() and '2000-01-01'. This is useful for calculating age or elapsed time from a fixed date to today.

Result

Interval showing how long ago the date was from now.

Understanding this shortcut saves you from writing explicit current date queries.

6

Advanced

How AGE Handles Month Length Variations

🤔Before reading on: do you think AGE counts months as fixed 30 days or actual calendar months? Commit to your answer.

Concept: AGE accounts for actual calendar month lengths, not fixed days.

Example: AGE('2024-03-31', '2024-02-29') returns '1 mon 2 days' because February has 29 days in 2024. AGE uses calendar logic to count months and leftover days accurately.

Result

Interval reflects real calendar months and days.

Knowing AGE respects calendar month lengths prevents errors in date difference calculations.

7

Expert

Surprising Behavior with Negative Intervals

🤔Before reading on: does AGE('2023-01-01', '2024-01-01') return a positive or negative interval? Commit to your answer.

Concept: AGE returns negative intervals when the first date is later than the second, but the sign applies to the whole interval, not individual parts.

Example: SELECT AGE('2023-01-01', '2024-01-01'); returns '-1 year'. The entire interval is negative, not just the year part. This can affect how you interpret or use the result in calculations.

Result

Negative interval indicating the first date is after the second.

Understanding interval sign behavior helps avoid bugs when comparing or adding intervals.

Under the Hood

AGE internally calculates the difference by first subtracting years, then months, then days, adjusting for calendar month lengths and leap years. It returns an interval type that stores these components separately. When timestamps are used, it also calculates hours, minutes, and seconds differences.

Why designed this way?

AGE was designed to provide human-friendly date differences that reflect real calendar time, unlike simple day counts. This design helps applications needing precise age or duration calculations that match how people think about time.

┌───────────────┐
│ Input Dates   │
│ (date1, date2)│
└──────┬────────┘
       │
       ▼
┌─────────────────────────────┐
│ Calculate Year Difference   │
│ Adjust for full years        │
└──────┬──────────────────────┘
       │
       ▼
┌─────────────────────────────┐
│ Calculate Month Difference  │
│ Adjust for full months       │
└──────┬──────────────────────┘
       │
       ▼
┌─────────────────────────────┐
│ Calculate Day Difference    │
│ Adjust for leftover days     │
└──────┬──────────────────────┘
       │
       ▼
┌─────────────────────────────┐
│ Compose Interval Result     │
│ (years mons days hours...)  │
└─────────────────────────────┘

Myth Busters - 4 Common Misconceptions

Quick: Does AGE('2024-06-01', '2023-06-01') return '365 days'? Commit yes or no.

Common Belief: AGE returns the difference as total days between dates.

Reality: AGE returns the difference as years, months, and days, not total days.

Why it matters: Assuming total days leads to wrong interpretations and calculations, especially when months and years matter.

Quick: Does AGE handle time parts when given timestamps? Commit yes or no.

Common Belief: AGE only works with dates and ignores time parts.

Reality: AGE works with timestamps and includes hours, minutes, and seconds in the result.

Why it matters: Ignoring time parts causes loss of precision in elapsed time calculations.

Quick: If the first date is earlier, does AGE return a positive interval? Commit yes or no.

Common Belief: AGE always returns a positive interval regardless of date order.

Reality: AGE returns a negative interval if the first date is later than the second.

Why it matters: Misunderstanding sign can cause logic errors in date comparisons and interval arithmetic.

Quick: Does AGE count months as fixed 30-day periods? Commit yes or no.

Common Belief: AGE treats all months as 30 days for simplicity.

Reality: AGE uses actual calendar month lengths, accounting for varying days and leap years.

Why it matters: Assuming fixed month lengths leads to inaccurate intervals and bugs in date calculations.

Expert Zone

1

AGE returns intervals with separate year, month, and day parts, which can behave differently than simple day counts in arithmetic.

2

When stacking AGE with other interval operations, the sign of the interval applies to the whole duration, not individual components, which can be subtle in calculations.

3

AGE's handling of leap years and varying month lengths means it can produce intervals that don't sum to the exact total days difference, which is intentional for human readability.

When NOT to use

Avoid AGE when you need exact total seconds or days difference; use EXTRACT(EPOCH FROM (date2 - date1)) or simple subtraction instead. For durations ignoring calendar months, intervals or timestamp subtraction are better.

Production Patterns

AGE is commonly used in user age calculations, subscription durations, and reporting elapsed time in readable formats. It is often combined with date_trunc for rounding and with interval arithmetic for billing or scheduling.

Connections

Interval Arithmetic

AGE returns intervals which can be added or subtracted using interval arithmetic.

Understanding AGE intervals helps you manipulate durations precisely in PostgreSQL queries.

Date and Time Data Types

AGE operates on date and timestamp types, building on their representation.

Knowing how dates and timestamps work is essential to correctly using AGE and interpreting its results.

Human Perception of Time

AGE models elapsed time as humans think about it: years, months, days, not just total seconds.

Recognizing this connection helps design user-friendly time displays and reports.

Common Pitfalls

#1 Using AGE expecting total days difference.

Wrong approach:

SELECT AGE('2024-06-01', '2023-06-01'); -- expecting '365 days'

Correct approach:

SELECT DATE '2024-06-01' - DATE '2023-06-01'; -- returns 366 (days)

Root cause:

Misunderstanding that AGE returns a human-readable interval, not a simple day count.

#2 Ignoring time parts when using AGE with timestamps.

Wrong approach:

SELECT AGE('2024-06-01 12:00', '2024-06-01 11:00'); -- expecting '0' or '1 day'

Correct approach:

SELECT AGE('2024-06-01 12:00', '2024-06-01 11:00'); -- returns '1 hour'

Root cause:

Not realizing AGE includes hours, minutes, and seconds when timestamps are used.

#3 Assuming AGE always returns positive intervals.

Wrong approach:

SELECT AGE('2023-01-01', '2024-01-01'); -- expecting positive interval

Correct approach:

SELECT AGE('2023-01-01', '2024-01-01'); -- returns '-1 year'

Root cause:

Not understanding that AGE returns negative intervals if first date is later.

Key Takeaways

The AGE function calculates the difference between two dates or timestamps as a human-readable interval of years, months, days, and time parts.

AGE respects calendar month lengths and leap years, making its results more natural than simple day counts.

When used with one argument, AGE returns the interval from that date to the current date/time.

AGE can return negative intervals if the first date is later than the second, affecting how you interpret results.

For exact total days or seconds difference, use simple subtraction or EXTRACT(EPOCH FROM ...), not AGE.

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