PostgreSQL – INTERVAL Data Type
The interval data type is used to represent a period of time. It stores the time span between two points in time, such as days, hours, minutes, and seconds. The interval data type can be used for various purposes, such as measuring durations, time-based calculations, and storing time intervals for scheduling or event management. Here are some key points about the interval data type:
- Format: Intervals can be represented in various formats, including years, months, days, hours, minutes, and seconds. The basic format is
X years Y months Z days H hours M minutes S seconds. - Interval Arithmetic: You can perform arithmetic operations on
intervalvalues, such as addition and subtraction. For example, you can calculate the difference between two timestamps to get aninterval. - Interval Functions: PostgreSQL provides various functions for working with
intervalvalues, such as extracting individual components (years, months, days, etc.), formatting intervals, and performing calculations. - Interval Ranges: Intervals can be both positive and negative. For example, a negative interval represents a duration in the past.
Here’s an example of using the interval data type:
CREATE TABLE meetings(meeting_id serial PRIMARY KEY,meeting_title varchar(200),meeting_duration interval );
INSERT INTO meetings (meeting_title, meeting_duration)VALUES ('Team Standup', '30 minutes');
SELECT * FROM meetingsWHERE meeting_duration > '15 minutes';
In this example, a meeting_duration column of type interval is used to store the duration of meetings. The interval data type is useful when you need to store and manipulate time spans or durations in your database.