postgresql
  1. postgresql-interval
PreviousNext

Interval - (PostgreSQL Data Types)

PostgreSQL supports a variety of data types for storing date and time data. One of these data types is interval, which represents a time interval or duration. In this tutorial, we'll cover the syntax, example, output, explanation, use, important points, and summary of interval data type in PostgreSQL.

Syntax

interval [fields] [precision]
  • fields: The interval fields to include in the data type (e.g. YEAR, MONTH, DAY, HOUR, etc.).
  • precision: The number of decimal places to include in the seconds field.

Example

Here's an example of how an interval data type can be used to store a time interval:

CREATE TABLE orders (
  order_id serial PRIMARY KEY,
  order_date date,
  delivery_time interval
);

INSERT INTO orders (order_id, order_date, delivery_time)
VALUES (1, '2022-10-14', interval '3 days 4 hours 30 minutes');

Output

When querying the orders table, the output would look like this:

order_id | order_date  | delivery_time      
---------+------------+--------------------
       1 | 2022-10-14 | 3 days 4:30:00

Explanation

In this example, we created a table called orders with columns for order_id, order_date, and delivery_time. The delivery_time column is assigned an interval data type and is set to '3 days 4 hours 30 minutes'.

Use

The interval data type is useful for storing time intervals or durations in PostgreSQL. This can be useful for tracking the difference between dates or timestamps, as well as for scheduling events or tasks.

Important Points

  • The interval data type can be used with both dates and timestamps.
  • The fields parameter is optional. If it is not specified, the default fields are YEAR, MONTH, DAY, HOUR, MINUTE, and SECOND.
  • The precision parameter is optional. If it is not specified, the default precision is 6 decimal places.

Summary

In this tutorial, we've covered the interval data type in PostgreSQL. We discussed the syntax, example, output, explanation, use, and important points of the interval data type. With this knowledge, you can now use the interval data type to store time intervals or durations in PostgreSQL, and use it in your queries and database design.

TimestampUUID
Published on: