The PostgreSQL EXTRACT
function is used to extract a specific subfield (such as year, month, day, hour) from a timestamp or interval. This function is essential for retrieving specific components of date and time values for various date/time calculations and comparisons.
EXTRACT(field FROM source)
The EXTRACT
function has the following components:
field
: The subfield to be extracted (e.g., 'year', 'month', 'day', 'hour').source
: The timestamp or interval from which the subfield will be extracted.Let's look at some examples of PostgreSQL EXTRACT
function queries:
SELECT EXTRACT(hour FROM timestamp '2001-02-16 20:38:40') AS hour_part;
This query extracts the hour subfield from the timestamp '2001-02-16 20:38:40', resulting in 20.
SELECT EXTRACT(month FROM interval '2 years 3 months') AS month_part;
This query extracts the month subfield from the interval '2 years 3 months', resulting in 3.
SELECT id, name, EXTRACT(day FROM event_timestamp) AS event_day
FROM events;
This query retrieves the id
, name
, and the day subfield extracted from the event timestamp for each row in the events
table.
Let's go through a complete example that includes creating a table, inserting data, and using the EXTRACT function to extract specific subfields from timestamps and intervals.
This step involves creating a new table named events
to store event data, including their timestamps and intervals.
CREATE TABLE events (
id SERIAL PRIMARY KEY,
name TEXT,
event_timestamp TIMESTAMPTZ,
duration INTERVAL
);
In this example, we create a table named events
with columns for id
, name
, event_timestamp
, and duration
.
This step involves inserting some sample data into the events
table.
INSERT INTO events (name, event_timestamp, duration)
VALUES ('Meeting', '2021-10-31 20:38:40+02', '2 hours 30 minutes'),
('Conference', '2022-05-15 14:00:00+02', '1 day'),
('Webinar', '2023-08-20 09:30:00+02', '3 hours');
Here, we insert data into the events
table.
This step involves using the EXTRACT()
function to extract specific subfields from the timestamps and intervals in the events
table.
Extract hour from event timestamp:
SELECT id, name, EXTRACT(hour FROM event_timestamp) AS event_hour
FROM events;
This query extracts the hour subfield from the event timestamp for each row in the events
table.
Extract day from event timestamp:
SELECT id, name, EXTRACT(day FROM event_timestamp) AS event_day
FROM events;
This query extracts the day subfield from the event timestamp for each row in the events
table.
Extract hour from event duration:
SELECT id, name, EXTRACT(hour FROM duration) AS duration_hours
FROM events;
This query extracts the hour subfield from the event duration for each row in the events
table.
The PostgreSQL EXTRACT()
function is a fundamental tool for retrieving specific subfields from timestamps and intervals. Understanding how to use the EXTRACT()
function and its syntax is essential for accurate date/time calculations and comparisons in PostgreSQL databases.