Using EXTRACT
You can use EXTRACT()
and DATE_PART()
to easily create new fields in your queries by extracting sub-fields from a source timestamp field.
Now suppose you want to produce a predictive model that will help forecast DVD rental activity by day of the week. You could use the EXTRACT()
function with the dow
field identifier in our query to create a new field called dayofweek
as a sub-field of the rental_date
column from the rental
table.
You can COUNT()
the number of records in the rental table for a given date range and aggregate by the newly created dayofweek
column.
This exercise is part of the course
Functions for Manipulating Data in PostgreSQL
Hands-on interactive exercise
Have a go at this exercise by completing this sample code.
SELECT
-- Extract day of week from rental_date
___(___ ___ ___) AS dayofweek
FROM rental
LIMIT 100;