Extracting Date Parts: EXTRACT() and AGE()

Today we’re diving back into working with temporal data, learning how to pull out specific parts (like year, month, or day of week) using the EXTRACT() function, and how to calculate age or time intervals between dates with AGE().

In real projects, working with temporal data often means you need to grab just a piece of a date or time. For example:

• Splitting orders by year or month;
• Counting users who registered on a certain day of the week;
• Analyzing how long it took between two events.

To solve stuff like this, we use EXTRACT() and AGE().

What is EXTRACT()?

The EXTRACT() function lets you pull out specific parts of a date or timestamp. For example, you can get the year from a birthdate, figure out the month number, or even grab the day of the week.

Syntax:

EXTRACT(part FROM source)

• part: the part of the date you want to extract. This could be YEAR, MONTH, DAY, HOUR, MINUTE, SECOND, and more.
• source: the temporal data type you’re pulling info from. This could be a column, a constant, or the result of a function.

Example 1: extracting year, month, and day

SELECT
    EXTRACT(YEAR FROM '2024-11-15'::DATE) AS year_part,
    EXTRACT(MONTH FROM '2024-11-15'::DATE) AS month_part,
    EXTRACT(DAY FROM '2024-11-15'::DATE) AS day_part;

Result:

Here we pulled out the year, month, and day from the date 2024-11-15. This is super useful when you want to group data by specific date parts.

Example 2: day of week and hour from time

SELECT
    EXTRACT(DOW FROM '2024-11-15'::DATE) AS day_of_week,
    EXTRACT(HOUR FROM '15:30:00'::TIME) AS hour_part;

Result:

• DOW (Day of Week) returns the day of week number: Sunday is 0, Monday is 1, and so on.
• HOUR grabs the hour from the time.

Example 3: using it on columns

If you have a table with dates, you can extract date parts for analysis. Let’s say you have an orders table:

SELECT
    order_id,
    EXTRACT(MONTH FROM order_date) AS month,
    EXTRACT(DAY FROM order_date) AS day
FROM orders;

Result:

What is AGE()?

The AGE() function is used to calculate the difference between two timestamps. For example, you can use it to figure out a customer’s age based on their birthdate, or see how much time has passed since an order was placed.

Syntax:

AGE(timestamp1, timestamp2)

• timestamp1: The later timestamp.
• timestamp2: The earlier timestamp.
• If you only give one parameter, PostgreSQL will compare it to the current date (NOW()).

Example 1: calculating age

SELECT AGE('2025-11-15'::DATE, '1990-05-12'::DATE) AS age;

Result:

This example shows the age of a person born on May 12, 1990, as of November 15, 2025.

Example 2: time interval between events

SELECT AGE('2023-06-01 15:00'::TIMESTAMP, '2023-05-20 10:30'::TIMESTAMP) AS duration;

Result:

Here we calculated the time interval between two events. This is handy when you want to know how long it took from start to finish for a task.

Example 3: customer age

Let’s say you have a customers table:

We can calculate the customers’ ages:

SELECT
    customer_id,
    AGE(NOW(), birth_date) AS age
FROM customers;

Result as of June 13, 2025:

Of course, you’ll have your own NOW() value and your own result.

Practical examples of using EXTRACT() and AGE()

Now let’s combine these functions in real-world scenarios.

Example 1: grouping data by month

Let’s say you have an orders table with dates. To count orders by month, use this query:

SELECT
    EXTRACT(MONTH FROM order_date) AS order_month,
    COUNT(*) AS total_orders
FROM orders
GROUP BY order_month
ORDER BY order_month;

Example 2: days until expiration

Imagine you have a subscriptions table:

We want to know how many days are left until the subscription expires:

SELECT
    subscription_id,
    AGE(expiry_date, NOW()) AS time_remaining
FROM subscriptions;

Result:

Common mistakes and how to avoid them

When using EXTRACT() and AGE(), beginners sometimes hit a few gotchas:

• Trying to extract an invalid part, like months from a TIME type. Remember: YEAR, MONTH, and DAY work with DATE and TIMESTAMP, but not with TIME.
• Issues with different temporal data formats. For example, the string 2023/11/15 isn’t a date. Use type casting with ::DATE or TO_DATE().
• The difference between AGE() and just subtracting timestamps. If you want an exact interval (in months, days, seconds), use AGE(). If you just want the number of days, simple arithmetic works.

Now you’ve got all the skills you need to extract and analyze temporal data parts in PostgreSQL. Go ahead and play around with EXTRACT() and AGE() in your own projects!