Understanding the Postgres extract() Function

Written by Team Timescale

The PostgreSQL extract() function allows you to extract a specific unit from a date. If you only want the year, month, or day, you can use extract() to access those values.

Syntax:

extract(<field> FROM <timestamp | interval>) Here is a list of the fields you can extract:

• century

• day

• decade

• dow (day of the week with Sunday as 0 and Saturday as 6)

• doy (day of the year)

• epoch

• hour

• isodow (day of the week with Monday as 1 and Sunday as 7)

• isoyear

• julian

• microseconds

• millennium

• milliseconds

• minute

• month

• quarter

• second

• timezone

• timezone_hour

• timezone_minute

• week

• year

Examples

For the first set of examples, we will use this weather data collected from two cities over a period of five days.

Extracting Month, Year, and Date

Using extract() is pretty simple. Here is a query that breaks up the day, month, and year of the date column into their own columns in the result set. Notice that we set aliases for each of the extracted fields and then can order the results by one of those aliases.

SELECT EXTRACT(DAY FROM measured) "day", EXTRACT(MONTH FROM measured) "month", EXTRACT(YEAR FROM measured) "year", city, temperature FROM city_data ORDER BY "day"; CopyAnd here are the results:

Grouping by an Extracted Value

The last example showed a simple use of extract() to extract various units from the results being returned by the query; we can sort by those extracted values. You can also group by those values to do aggregate queries based on them.

Here is one way we could get the average value of the temperature between both cities for each day. While we could just group by the date itself, this demonstrates how you can do more advanced queries on tables that contain dates using extract().

SELECT EXTRACT(DAY FROM measured) "day", avg(temperature) FROM city_data GROUP BY "day" ORDER BY "day"; CopyHere are the results:

Advanced Examples

For the next example, we are going to use data that looks like this from a table called conditions. It contains temperature and humidity information from 200 separate sensors taken every two minutes.

In this query, we want to get the average temperature for each device for each hour of the day, so we extract the hour from the time column and group the results by the hour and the device_id.

SELECT   device_id,   EXTRACT(hour from time) as hours,   avg(temperature) FROM conditions GROUP BY hours, device_id;Copy

Here is an example of some of the results:

Here is another example using the same set of data that counts how many readings were taken in each 10-minute interval. We can use the trunc() function to aggregate the readings by 10-minute intervals.

SELECT   EXTRACT(hour from time) as hours,   trunc(EXTRACT(minute from time) / 10)*10 AS ten_mins,   COUNT(*) FROM conditions GROUP BY hours, ten_mins; CopyWhile the sensors in this table were pretty consistent in their readings, if one of the counts were off, it would show how a sensor could be malfunctioning or that there are network issues.

Here is the first part of the results:

Next Steps

To learn more about using extract() in PostgreSQL, you can read our article on date/time functions. For more examples on how to use extract() in your own TimescaleDB SQL queries, see these Timescale documentation sections:

• Perform Advanced Analytic Queries

• Time and Continuous Aggregates

• How to Write Better Queries for Time-Series Data Analysis With Custom SQL Functions