PostgreSQL DATE_TRUNC Date/Time Function

PostgreSQL DATE_TRUNC Date/Time Function

The PostgreSQL DATE_TRUNC function is used to truncate a timestamp or interval to a specified precision. This function is essential for rounding date and time values to a desired level of granularity for various date/time calculations and comparisons.

Syntax

DATE_TRUNC(field, source)

The DATE_TRUNC function has the following components:

  • field: The precision to which the timestamp or interval will be truncated (e.g., 'year', 'month', 'day', 'hour').
  • source: The timestamp or interval to be truncated.

Example PostgreSQL DATE_TRUNC Queries

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

1. DATE_TRUNC with Timestamp

SELECT DATE_TRUNC('hour', timestamp '2001-02-16 20:38:40') AS truncated_timestamp;

This query truncates the timestamp '2001-02-16 20:38:40' to the hour precision, resulting in '2001-02-16 20:00:00'.

2. DATE_TRUNC with Timestamp with Time Zone

SELECT DATE_TRUNC('day', timestamptz '2001-02-16 20:38:40+00', 'Australia/Sydney') AS truncated_timestamp;

This query truncates the timestamp '2001-02-16 20:38:40+00' to the day precision in the 'Australia/Sydney' time zone, resulting in '2001-02-16 13:00:00+00'.

3. DATE_TRUNC with Interval

SELECT DATE_TRUNC('hour', interval '2 days 3 hours 40 minutes') AS truncated_interval;

This query truncates the interval '2 days 3 hours 40 minutes' to the hour precision, resulting in '2 days 03:00:00'.

4. DATE_TRUNC with Column Values

SELECT id, name, DATE_TRUNC('minute', event_timestamp) AS truncated_event_timestamp
FROM events;

This query retrieves the id, name, and the event timestamp truncated to the minute precision 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_TRUNC function to truncate timestamps and intervals to specific precisions.

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

This step involves using the DATE_TRUNC() function to truncate timestamps and intervals to specific precisions in the events table.

Truncate event timestamp to the hour:

SELECT id, name, DATE_TRUNC('hour', event_timestamp) AS truncated_event_timestamp
FROM events;

This query truncates the event timestamp to the hour precision for each row in the events table.

Truncate event timestamp to the day in a specified time zone:

SELECT id, name, DATE_TRUNC('day', event_timestamp, 'Australia/Sydney') AS truncated_event_timestamp
FROM events;

This query truncates the event timestamp to the day precision in the 'Australia/Sydney' time zone for each row in the events table.

Truncate event duration to the hour:

SELECT id, name, DATE_TRUNC('hour', duration) AS truncated_duration
FROM events;

This query truncates the event duration to the hour precision for each row in the events table.

Conclusion

The PostgreSQL DATE_TRUNC() function is a fundamental tool for truncating timestamps and intervals to a specified precision. Understanding how to use the DATE_TRUNC() function and its syntax is essential for accurate date/time calculations and comparisons in PostgreSQL databases.