PostgreSQL EXTRACT Function

Summary: In this tutorial, you’ll learn to extract a date field such as year, month, or day from a date using the PostgreSQL EXTRACT function.

PostgreSQL EXTRACT Function Overview #

The EXTRACT function allows you to extract a date field from a date.

Here’s the syntax of the EXTRACT function:

EXTRACT(field FROM source)Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

In this syntax:

field is a date field you want to extract. It can be a year, month, day, hour, minute, second, etc.
source is a date from which you want to extract a field. The date can be a TIME, DATE, or TIMESTAMP value.

The EXTRACT function returns a field value from the source. It returns NULL if either field or source is NULL.

Basic PostgreSQL EXTRACT Function Example #

The following example uses the EXTRACT function to get the year, month, and date from the date '2025-03-18':

SELECT
  EXTRACT(
    YEAR
    FROM
      DATE '2025-03-18'
  ) y,
  EXTRACT(
    MONTH
    FROM
      DATE '2025-03-18'
  ) m,
  EXTRACT(
    DAY
    FROM
      DATE '2025-03-18'
  ) d;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Output:

  y   | m | d
------+---+----
 2025 | 3 | 18Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Using EXTRACT Function with Table Data #

Suppose we have the following transactions table:

The following example uses the EXTRACT function to extract year, month, and day from the transaction_date column of the transactions table:

SELECT
  transaction_id,
  EXTRACT(
    YEAR
    FROM
      transaction_date
  ) transaction_year,
  EXTRACT(
    MONTH
    FROM
      transaction_date
  ) transaction_month,
  EXTRACT(
    DAY
    FROM
      transaction_date
  ) transaction_day
FROM
  transactions;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Output:

 transaction_id | transaction_year | transaction_month | transaction_day
----------------+------------------+-------------------+-----------------
              1 |             2024 |                12 |               1
              2 |             2024 |                12 |               1
              3 |             2024 |                12 |               1
              4 |             2024 |                12 |               2
              5 |             2024 |                12 |               2
              6 |             2024 |                12 |               2
              7 |             2024 |                12 |               2
              8 |             2024 |                12 |               3
              9 |             2024 |                12 |               3
             10 |             2024 |                12 |               3
...Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Using the EXTRACT Function with Aggregate Functions #

The following example uses the EXTRACT function with the SUM function and GROUP BY clause to get the total quantity received by year and month:

SELECT
  EXTRACT(
    YEAR
    FROM
      transaction_date
  ) transaction_year,
  EXTRACT(
    MONTH
    FROM
      transaction_date
  ) transaction_month,
  SUM(quantity) qty
FROM
  transactions
WHERE
  type = 'receipt'
GROUP BY
  transaction_year,
  transaction_month;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Output:

 transaction_year | transaction_month | qty
------------------+-------------------+-----
             2024 |                12 | 210Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Summary #

Use the PostgreSQL EXTRACT function to extract a specific date field from a date.

Quiz #

Was this tutorial helpful ?