PostgreSQL EXTRACT Date/Time Function

PostgreSQL EXTRACT Date/Time Function

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.

---

Syntax

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.

---

Example PostgreSQL EXTRACT Queries

Let's look at some examples of PostgreSQL EXTRACT function queries:

1. EXTRACT with Timestamp

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.

2. EXTRACT with Interval

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.

3. EXTRACT with Column Values

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.

---

Full Example

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.

Step 1: Creating a Table

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.

Step 2: Inserting Data into the Table

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.

Step 3: Using the EXTRACT Function

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.

---

Conclusion

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.