PostgreSQL DATE_BIN Date/Time Function

PostgreSQL DATE_BIN Date/Time Function

The PostgreSQL DATE_BIN function is used to bin a timestamp into a specified interval aligned with a specified origin. This function is essential for grouping timestamps into regular intervals for time series analysis.

Syntax

DATE_BIN(interval, timestamp, origin)

The DATE_BIN function has the following components:

  • interval: The interval for binning the timestamp (e.g., '15 minutes').
  • timestamp: The timestamp to be binned.
  • origin: The origin timestamp to align the binning intervals.

Example PostgreSQL DATE_BIN Queries

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

1. Basic DATE_BIN Example

SELECT DATE_BIN('15 minutes', timestamp '2001-02-16 20:38:40', timestamp '2001-02-16 20:05:00') AS binned_timestamp;

This query bins the timestamp '2001-02-16 20:38:40' into 15-minute intervals aligned with the origin '2001-02-16 20:05:00', resulting in '2001-02-16 20:35:00'.

2. DATE_BIN with Hourly Interval

SELECT DATE_BIN('1 hour', timestamp '2023-08-20 09:45:00', timestamp '2023-08-20 00:00:00') AS binned_timestamp;

This query bins the timestamp '2023-08-20 09:45:00' into 1-hour intervals aligned with the origin '2023-08-20 00:00:00', resulting in '2023-08-20 09:00:00'.

3. DATE_BIN with Column Values

SELECT id, name, DATE_BIN('30 minutes', event_timestamp, timestamp '2023-08-20 00:00:00') AS binned_event_timestamp
FROM events;

This query retrieves the id, name, and the event timestamp binned into 30-minute intervals aligned with the origin '2023-08-20 00:00:00' 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_BIN function to bin timestamps into regular 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
);

In this example, we create a table named events with columns for id, name, and event_timestamp.

Step 2: Inserting Data into the Table

This step involves inserting some sample data into the events table.

INSERT INTO events (name, event_timestamp)
VALUES ('Meeting', '2021-10-31 20:38:40+02'),
       ('Conference', '2022-05-15 14:45:00+02'),
       ('Webinar', '2023-08-20 09:30:00+02');

Here, we insert data into the events table.

Step 3: Using the DATE_BIN Function

This step involves using the DATE_BIN() function to bin the timestamps into regular intervals in the events table.

Bin timestamp into 15-minute intervals:

SELECT id, name, DATE_BIN('15 minutes', event_timestamp, timestamp '2021-10-31 20:05:00+02') AS binned_event_timestamp
FROM events;

This query bins the event timestamps into 15-minute intervals aligned with the origin '2021-10-31 20:05:00+02' for each row in the events table.

Bin timestamp into hourly intervals:

SELECT id, name, DATE_BIN('1 hour', event_timestamp, timestamp '2023-08-20 00:00:00+02') AS binned_event_timestamp
FROM events;

This query bins the event timestamps into 1-hour intervals aligned with the origin '2023-08-20 00:00:00+02' for each row in the events table.

Conclusion

The PostgreSQL DATE_BIN() function is a fundamental tool for grouping timestamps into regular intervals for time series analysis. Understanding how to use the DATE_BIN() function and its syntax is essential for effective time-based data analysis in PostgreSQL databases.