PostgreSQL DATE_ADD Date/Time Function

PostgreSQL DATE_ADD Date/Time Function

The PostgreSQL DATE_ADD function is used to add an interval to a timestamp with time zone, computing times of day and daylight-savings adjustments according to the specified time zone. If the time zone is omitted, the current TimeZone setting is used. This function is essential for performing date and time arithmetic, taking into account time zone differences.

Syntax

DATE_ADD(timestamp with time zone, interval [, text])

The DATE_ADD function has the following components:

  • timestamp with time zone: The timestamp to which the interval will be added.
  • interval: The interval to add to the timestamp.
  • text: The optional time zone in which to compute the result.

Example PostgreSQL DATE_ADD Queries

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

1. Basic DATE_ADD Example

SELECT DATE_ADD('2021-10-31 00:00:00+02'::timestamptz, '1 day'::interval) AS new_timestamp;

This query adds a 1-day interval to the timestamp '2021-10-31 00:00:00+02', resulting in '2021-11-01 00:00:00+02'.

2. DATE_ADD with Specified Time Zone

SELECT DATE_ADD('2021-10-31 00:00:00+02'::timestamptz, '1 day'::interval, 'Europe/Warsaw') AS new_timestamp;

This query adds a 1-day interval to the timestamp '2021-10-31 00:00:00+02', computing the result in the 'Europe/Warsaw' time zone, resulting in '2021-10-31 23:00:00+00' due to daylight saving time adjustment.

3. DATE_ADD with Column Values

SELECT id, name, DATE_ADD(event_timestamp, '1 hour'::interval, 'America/New_York') AS new_event_timestamp
FROM events;

This query retrieves the id, name, and the event timestamp with a 1-hour interval added, computing the result in the 'America/New_York' time zone 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_ADD function to perform date and time arithmetic.

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 00:00:00+02'),
       ('Conference', '2022-05-15 14:00:00+02'),
       ('Webinar', '2023-08-20 09:30:00+02');

Here, we insert data into the events table.

Step 3: Using the DATE_ADD Function

This step involves using the DATE_ADD() function to perform date and time arithmetic in the events table.

Add a 1-day interval to the event timestamp:

SELECT id, name, DATE_ADD(event_timestamp, '1 day'::interval) AS new_event_timestamp
FROM events;

This query adds a 1-day interval to the event timestamp for each row in the events table.

Add a 1-hour interval to the event timestamp with specified time zone:

SELECT id, name, DATE_ADD(event_timestamp, '1 hour'::interval, 'America/New_York') AS new_event_timestamp
FROM events;

This query adds a 1-hour interval to the event timestamp, computing the result in the 'America/New_York' time zone for each row in the events table.

Conclusion

The PostgreSQL DATE_ADD() function is a fundamental tool for performing date and time arithmetic, taking into account time zone differences. Understanding how to use the DATE_ADD() function and its syntax is essential for accurate date and time operations in PostgreSQL databases.