Understanding the Postgres extract() Function

Start supercharging your PostgreSQL today.

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.

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. For more examples on how to use extract() in your own TimescaleDB SQL queries, see these Timescale documentation sections: