PostgreSQL Interval Data Type (With Examples)

In the previous tutorial, we have seen the date/time datatypes which also include the interval type. However, because interval type itself is a vast topic, everything could not be covered in a single tutorial. Therefore, we will learn about interval type in this tutorial. So, let’s get started!

Also Read: PostgreSQL Integer Types: SMALLINT, INT, BIGINT

Introduction to INTERVAL Type

In interval type, you can mention the period of time such as years, months, days, hours, minutes, seconds etc. The difference between date/time and interval is that you specify date/time in the string format without any unit, whereas, in the interval type, you specify the unit name such as years, days, minutes etc.

You can use the interval as a data type for the column, or as a function to add the time period in the existing timestamp.

In PostgreSQL, interval supports the following time period units or their plural forms:

microsecond, millisecond, second, minute, hour, day, week, month, year, decade, century, millennium

The interval type takes 16 bytes of storage space which can store from –178000000 years to 178000000 years.

Now let’s see the syntax of the interval type.

Syntax of INTERVAL Type

Following is the syntax of the INTERVAL type.

[@] interval [ fields ] [ (p) ]   Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Here,

• @ – it is an optional parameter, therefore you can skip it.
• fields – the time period such as year, days etc
• p – it specifies the precision value for the seconds. It ranges from 0 to 6.

Example:

INTERVAL YEAR; -- only year
INTERVAL SECONDS(5); -- precision works only for seconds , Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Now let’s see which formats of interval value are accepted in PostgreSQL.

PostgreSQL INTERVAL Input Format

To specify an interval value, we follow the below syntax:

[@] quantity unit [quantity unit...] [direction];Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Here,

• @ – it is an optional parameter
• quantity – It is a number
• unit – It is a measure of a time period such as days, hours, minutes etc.
• direction – a direction can be an empty parameter or ‘ago’. The ‘ago’ negates the time.

Available units are already discussed above in the introduction part. You can specify many quantities-units while specifying an interval value. We will see this by performing an example below.

You can also specify an interval value in ISO 8601 format which looks like this:

P quantity unit [ quantity unit ...] [ T [ quantity unit ...]]Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Here, the value must start with the letter p and at least one quantity should be present. Other parameters are optional. Here, T represents the time-of-day units.

Note that, you can omit the units and can be specified in any order. However, units smaller than a day must come after the T. Look at the table below for the units in ISO 8601.

Here, the letter M is used for both months and minutes. However, the meaning of M depends on which side it appears on, before or after T.

For sample formats of inputs, you can click here.

PostgreSQL INTERVAL Output Format

There are four output formats for the interval output value: postgres, postgres_verbose, sql_standard, and iso_8601.

You can set the output format using the below command:

SET INTERVALSTYLE = 'type'Code language: JavaScript (javascript)

The following table gives a glimpse of different output format examples.

Now let’s see some examples of the interval type.

PostgreSQL INTERVAL Examples

Let’s first see a basic example without creating a table.

SELECT DATE(NOW())+INTERVAL'1 YEAR'
AS timestamp;

SELECT DATE(NOW())+INTERVAL'1 YEAR 10 SECONDS'
AS timestamp;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Here, the first query consists of the current date and we add an interval of 1 year. The same goes with the second query with the addition of seconds as well.

Let’s see the output of the above queries.

As you can see, the output timestamp is 1 year next to the current date-time.

Here, you can also specify the ‘ago’ keyword to subtract the interval. Let’s see an example.

SELECT DATE(NOW())+INTERVAL'1 YEAR ago'
AS timestamp;

SELECT DATE(NOW())+INTERVAL'1 YEAR 10 SECONDS ago'
AS timestamp;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

As you can see in the output, PostgreSQL has correctly calculated the resulting timestamp as per our requirement.

Now let’s create a table with the interval type.

Here, we will create a table to store the registration information.

In the table, we will have a column called ‘lastseen‘ of type interval. Using this column value, we will manipulate the other values. Let’s see.

drop table if exists registrations ;
CREATE TABLE registrations(
id SERIAL PRIMARY KEY,
name VARCHAR(100) NOT NULL,
dateRegistered DATE NOT NULL,
timeRegistered TIME NOT NULL,
entryTime TIMESTAMP(5) DEFAULT CURRENT_TIMESTAMP,
lastseen INTERVAL
);
Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Here, the interval column doesn’t have a field specified. That means, it can accept any unit and multiple values. Now let’s insert some records into the table.

INSERT INTO registrations (name, dateRegistered, timeRegistered, lastseen)
VALUES
('John Doe', '2022-01-15', '13:45:00', '1 year 3 months 10 days'),
('Jane Smith', '2022-02-20', '10:30:00', '2 years 6 months 3 days'),
('Bob Johnson', '2022-03-05', '16:15:00', '1 year'),
('Alice Johnson', '2022-04-10', '09:00:00', '3 years'),
('Ethan Chen', '2022-05-15', '14:30:00', '2 years 3 months'),
('Michael Wang', '2022-07-25', '18:00:00', '6 months');Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Here, we have used the interval values for the last seen column. We will subtract the last seen column value from the timeregistered column value and display it. But first, let’s display the table data.

SELECT * FROM registrations;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

As you can see, the lastseen column shows the value in the same format we inserted them.

Let’s write a query for the subtraction and display the result.

SELECT name,dateregistered,timeregistered,entrytime, 
date(dateregistered - lastseen) as lastseen  
FROM registrations;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Here, we have subtracted the registered date and the last seen values. Note that, this doesn’t make any sense but we are doing this for the demonstrating purpose so that you can learn how interval values can be used.

Let’s see the output now.

As you can see, we got the date that is exactly earlier by the last seen value than the registered date.

Now let’s see different interval format output examples.

By default, the ‘postgres‘ format is set. Let’s set the interval format to ‘sql_standard‘ now.

SET INTERVALSTYLE='sql_standard';Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Here, the output is in the SQL standard. The First three values are for years-months-days and the next three parameters are for hours-minutes-seconds.

Now let’s see the output for the ‘iso_8601’ style.

SET INTERVALSTYLE='iso_8601';Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

You can check out the output for the remaining format.

Conclusion

The INTERVAL type is a considerably interesting topic and challenging to learn. The more you practise, the more you learn it in depth. We recommend you read the official documentation as well and try things out yourself for a clear understanding. Also, to manipulate the interval values, there are a bunch of functions available in PostgreSQL which we will see in another tutorial.

Reference

PostgreSQL Official Documentation