--- title: "Understanding Postgres EXTRACT Function" description: "Read how you can use the Postgres extract() function, including the list of fields you can extract, and practical examples." section: "Postgres basics" --- > **TimescaleDB is now Tiger Data.** *Written by Team Timescale* The PostgreSQL [extract()](https://www.postgresql.org/docs/14/functions-datetime.html) 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( FROM ) 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. | measured | city | temperature | precipitation | | --- | --- | --- | --- | | 2021-09-01 | Miami | 65.30 | 0.28 | | 2021-09-01 | Atlanta | 63.14 | 0.20 | | 2021-09-02 | Miami | 64.40 | 0.79 | | 2021-09-02 | Atlanta | 62.60 | 0.59 | | 2021-09-03 | Atlanta | 62.60 | 0.39 | | 2021-09-03 | Miami | 71.60 | 0.47 | | 2021-09-04 | Miami | 68.36 | 0.00 | | 2021-09-04 | Atlanta | 67.28 | 0.00 | | 2021-09-05 | Miami | 72.50 | 0.00 | | 2021-09-05 | Atlanta | 70.80 | 0.00 | ### **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"; `And here are the results: | day | month | year | city | temperature | | --- | --- | --- | --- | --- | | 1 | 9 | 2021 | Miami | 65.30 | | 1 | 9 | 2021 | Atlanta | 63.14 | | 2 | 9 | 2021 | Atlanta | 62.60 | | 2 | 9 | 2021 | Miami | 64.40 | | 3 | 9 | 2021 | Atlanta | 62.60 | | 3 | 9 | 2021 | Miami | 71.60 | | 4 | 9 | 2021 | Miami | 68.36 | | 4 | 9 | 2021 | Atlanta | 67.28 | | 5 | 9 | 2021 | Miami | 72.50 | | 5 | 9 | 2021 | Atlanta | 70.80 | ### **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"; `Here are the results: | day | avg | | --- | --- | | 1 | 64.22 | | 2 | 63.50 | | 3 | 67.10 | | 4 | 67.82 | | 5 | 71.65 | ### **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. | time | device_id | temperature | humidity | | --- | --- | --- | --- | | 2016-11-16 16:18:00.000 -0600 | weather-pro-000279 | 42 | 48 | | 2016-11-16 16:18:00.000 -0600 | weather-pro-000000 | 42 | 54.6 | | 2016-11-16 16:18:00.000 -0600 | weather-pro-000001 | 42 | 54.4 | | 2016-11-16 16:18:00.000 -0600 | weather-pro-000002 | 42 | 55.2 | | 2016-11-16 16:18:00.000 -0600 | weather-pro-000003 | 42 | 52.7 | | 2016-11-16 16:18:00.000 -0600 | weather-pro-000004 | 70 | 49 | 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;` Here is an example of some of the results: | device_id | hours | avg | | --- | --- | --- | | weather-pro-000000 | 0 | 38.0599999999999446 | | weather-pro-000001 | 0 | 37.9699999999999428 | | weather-pro-000002 | 0 | 36.9233333333332606 | | weather-pro-000003 | 0 | 36.8799999999999271 | | weather-pro-000004 | 0 | 61.9766666666666389 | | weather-pro-000005 | 0 | 62.7766666666666493 | | weather-pro-000006 | 0 | 37.8733333333332746 | 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; `While 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: | hours | ten_mins | count | | --- | --- | --- | | 0 | 0 | 5000 | | 0 | 10 | 5000 | | 0 | 20 | 5000 | | 0 | 30 | 5000 | | 0 | 40 | 5000 | ## **Next Steps** To learn more about using extract() in PostgreSQL, you can read our article on [date/time functions.](https://www.timescale.com/learn/postgresql-date-and-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](https://docs.timescale.com/timescaledb/latest/how-to-guides/query-data/advanced-analytic-queries/) - [Time and Continuous Aggregates](https://docs.timescale.com/timescaledb/latest/how-to-guides/continuous-aggregates/time/) - [How to Write Better Queries for Time-Series Data Analysis With Custom SQL Functions](https://www.timescale.com/blog/how-to-write-better-queries-for-time-series-data-analysis-using-custom-sql-functions/)