# How to Use EXTRACT() Function in PostgreSQL

> The EXTRACT() function in PostgreSQL extracts a specific field, like a day, month, minutes, hours, etc., from an INTERVAL or TIMESTAMP.

The **EXTRACT()** function in PostgreSQL extracts a specific field, like a day, month, minutes, hours, etc., from a date or time value. It accepts a ‘source’ and a ‘field’ as arguments and returns an extracted field from the given source. The returned value will be a double precision value.

This write-up will discuss the working of the **EXTRACT()** function using practical examples. So, let’s begin.

###  **How to Use EXTRACT() Function in PostgreSQL?**

To comprehend the concept of the **EXTRACT()** function, firstly, we need to understand its syntax:
    
    
    EXTRACT(Field FROM Source);

In the extract function, the first argument is a field that specifies which field should be extracted from the given source(date/time value). FROM is a keyword, while the second argument is a source that will be one of the following types: INTERVAL or TIMESTAMP. If we pass a DATE type value to the **EXTRACT()** function, then the **EXTRACT()** function will cast/convert it into a TIMESTAMP value.

The valid field formats are listed below:

\- MILLENNIUM, CENTURY, DECADE, YEAR, DOY(Day of Year between 1 and 366), ISODOY(week number of the year based on ISO 8601), or QUARTER.

\- MONTH, WEEK, DOW(Day of Week 0 represents Sunday while 6 represents Saturday), ISODOW(ISO-based Day of Week 7 represents Sunday while 1 represents Monday), or DAY.

\- HOUR, MINUTE, SECONDS, EPOCH(Total seconds since 1970-01-01), MICROSECONDS, or MILLISECONDS.

\- TIMEZONE, TIMEZONE_HOUR, or TIMEZONE_MINUTE.

 **Note:** DOW, ISODOW, DOY, ISODOY, TIMEZONE, TIMEZONE_HOUR, and TIMEZONE_MINUTE are invalid field formats for the INTERVAL.

###  **Example #1: How to Extract a DAY From TIMESTAMP?**

Let’s execute the below-mentioned command to extract a day from the specified TIMESTAMP:
    
    
    SELECT EXTRACT('DAY' FROM TIMESTAMP '2022-08-29 02:28:15');

The **EXTRACT()** function retrieved the day successfully.

###  **Example #2: How to Extract a YEAR From TIMESTAMP?**

To extract a year from the given TIMESTAMP, we will execute the below-mentioned query:
    
    
    SELECT EXTRACT('YEAR' FROM TIMESTAMP '2022-08-29 02:28:15');

The **EXTRACT()** function extracted the year from the given timestamp.

###  **Example #3: How to Extract a QUARTER From TIMESTAMP?**

In this example, we will utilize the **EXTRACT()** function to extract the quarter from the given TIMESTAMP:
    
    
    SELECT EXTRACT('QUARTER' FROM TIMESTAMP '2022-08-29 02:28:15');

There are four quarters in a calendar year. August lies in the third quarter, so the EXTRACT() function returns 3.

###  **Example #4: How to Extract a DOY From TIMESTAMP?**

Let’s execute the **EXTRACT()** function to extract the day of the year from the given TIMESTAMP:
    
    
    SELECT EXTRACT('DOY' FROM TIMESTAMP '2022-08-29 02:28:15');

The output shows that it’s the 241st day of the year.

###  **Example #5: How to Extract a DAY From the Current Date?**

In Postgres, the CURRENT_DATE retrieves the latest/current date as shown in the following snippet:
    
    
    SELECT CURRENT_DATE;

Output shows that it's the 29th of august 2022. Let’s execute the following query to extract the day from the current date:
    
    
    SELECT EXTRACT('DAY' FROM CURRENT_DATE);

In the **EXTRACT()** function, we specified the ‘DAY’ as the first argument and the CURRENT_DATE function as the second argument. Consequently, we will get the following outcome:

The **EXTRACT()** function successfully extracted the specified field from the current date.

###  **Example #6: How to Extract a Year From a TIMESTAMP Using the Table’s Data?**

We have created a table named bike_details. Let’s execute the SELECT statement to fetch its details:
    
    
    SELECT * FROM bike_details;

Suppose the need of the moment is to fetch the launching year of each bike. To do this, we will utilize the **EXTRACT()** function as follows:
    
    
    SELECT EXTRACT('YEAR' FROM bike_launch_date)
    FROM bike_details;

From the bike_launch_date column, the **EXTRACT()** function successfully extracted the launching year for each bike.

This way, you can specify any valid field in the **EXTRACT()** function to extract any specific part/field from the given TIMESTAMP.

###  **Example #7: How to Extract a YEAR From an Interval?**

In the **EXTRACT()** function, we will specify a **YEAR** as the first argument and an interval as the second argument:
    
    
    SELECT EXTRACT('YEAR' FROM INTERVAL '10 years 6 months 15 days 5 hours 12 minutes 14 second');

On executing the query mentioned above, the following output will appear:

This is how you can extract a year from an INTERVAL.

###  **Example #8: How to Extract HOURS From an Interval?**

This example will demonstrate how to extract a day of the week from an interval:
    
    
    SELECT EXTRACT('HOUR' FROM INTERVAL '10 years 6 months 15 days 5 hours 12 minutes 14 second');

The output verifies the working of the **EXTRACT()** function.

###  **Example #9: How to Extract CENTURY From an Interval?**

Let’s extract a century from the given INTERVAL using the **EXTRACT()** function:
    
    
    SELECT EXTRACT('CENTURY' FROM INTERVAL '2022 years 8 months 29 days 3 hours 55 minutes 14 second');

The output indicates that it is the 20th century in the specified INTERVAL.

###  **Example #10: How to Extract MONTH From an Interval Using Table’s Data?**

Suppose we have to extract the month from the employee_example table. First, we will check the table details using the following statement:
    
    
    SELECT * FROM employee_example;

Now, let’s utilize the **EXTRACT()** function to extract the MONTH from the joining_date column:
    
    
    SELECT EXTRACT('MONTH' FROM joining_date)
    FROM employee_example;

This way, you can extract any field from the specified INTERVAL using the **EXTRACT()** method.

###  **Conclusion**

The **EXTRACT()** function in PostgreSQL extracts a specific field, like a day, month, minutes, hours, etc., from an INTERVAL or TIMESTAMP. There are multiple valid field formats that are used to extract a date or time from an INTERVAL or TIMESTAMP. This write-up demonstrated the working of the **EXTRACT()** function along with examples.

---
[View this page online](https://www.commandprompt.com/education/how-to-use-extract-function-in-postgresql/)