PostgreSQL DATE_PART Date/Time Function

PostgreSQL DATE_PART Date/Time Function

The PostgreSQL DATE_PART 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

DATE_PART(field, source)

The DATE_PART 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 DATE_PART Queries

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

1. DATE_PART with Timestamp

SELECT DATE_PART('hour', 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. DATE_PART with Interval

SELECT DATE_PART('month', 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. DATE_PART with Column Values

SELECT id, name, DATE_PART('day', 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 DATE_PART 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.

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 DATE_PART Function

This step involves using the DATE_PART() function to extract specific subfields from the timestamps and intervals in the events table.

Extract hour from event timestamp:

SELECT id, name, DATE_PART('hour', 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 duration:

SELECT id, name, DATE_PART('day', duration) AS duration_days
FROM events;

This query extracts the day subfield from the event duration for each row in the events table.

Conclusion

The PostgreSQL DATE_PART() function is a fundamental tool for retrieving specific subfields from timestamps and intervals. Understanding how to use the DATE_PART() function and its syntax is essential for accurate date/time calculations and comparisons in PostgreSQL databases.