date_part extracts a named component from a date or timestamp. It is useful when imported time-series data needs cleaning or when dates must become grouping keys. section 3.4.3 section 3.4.4
Use the function by supplying the requested part as its first argument:
- Extract minutes with date_part('minute', read_on), then filter readings to accepted minute marks. section 3.4.3
- Extract years with date_part('year', valid_from), alias the result, and group aggregates by that alias. section 3.4.4
- Choose identifiers such as 'day', 'hour', or 'minute'; dedicated functions such as year() may be easier to read. section 3.4.4
Remove readings from unexpected minutes
DELETE FROM readings WHERE date_part('minute', read_on) NOT IN (0,15,30,45); The condition keeps quarter-hour readings and deletes rows measured at other minutes. For data whose timing irregularities are known before ingestion, time_bucket can instead be used during ingestion. section 3.4.3
Group prices by year
SELECT date_part('year', valid_from) AS year, min(value) AS minimum_price, max(value) AS maximum_price FROM prices WHERE year BETWEEN 2019 AND 2020 GROUP BY year ORDER BY year; The alias makes the extracted year readable and serves as the grouping key, producing minimum and maximum prices for each year.
Listing 3.9 Cleaning the ingested data
DELETE FROM readings
WHERE date_part('minute', read_on) NOT IN (0,15,30,45);
Open the cleaning listing to inspect the complete minute-based DELETE statement. section 3.4.3
