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.
