Redshift Interval Data Types and Conversion Examples

Amazon Redshift supports interval types in the same way as other postgreSQL databases such as Netezza, Vertica, Oracle, etc. It accepts interval syntax with unit specifications. You have to specify the units along withe interval value. In this article we will check Redshift interval data types and conversion Examples.

Redshift Interval Data Types

Use an interval literal to identify specific periods of time, such as 10 hours or 6 days. You can use these interval literals in conditions and calculations that involve date-time expressions. Redshift accepts the interval syntax, but ignores the unit specification. All intervals are the same, and can contain values of any combination of units.

An interval is expressed as a combination of the INTERVAL keyword with a numeric quantity and a supported datepart; for example: INTERVAL '1 days' or INTERVAL '10 minutes'.

You can form a precise interval type by combining multiple units. For examples, INTERVAL ‘1 days, 2 hours, 59 minutes’.

Abbreviations and plurals of each unit are also supported; for example: 5 s, 5 second, and 5 seconds are equivalent intervals.

Redshift interval literals

Below are the Redshift interval literals:

Supported Interval Description	Examples along with units
seconds/second/s	INTERVAL ’10 seconds’
minutes/ minute/m	INTERVAL ’20 minutes’
hours/hour/h	INTERVAL ‘1 hour’
days/day/d	INTERVAL ‘1 day’
weeks/week/w	INTERVAL ‘6 weeks’
months/month	INTERVAL ‘1month’
years/year	INTERVAL ‘2 years’

Redshift Interval Types Implementation

You can implement an INTERVAL data type in a same or slightly different way compared to other databases.

Redshift allows you to specify interval qualifiers such as years, months, weeks, days, etc.
You can specify the quantity value as a fraction. For example, 0.5 days
In Redshift, interval literals must include the units like ‘1 days’. If you do not specify a datepart or literals, the interval value represents seconds.

Create Redshift Table with Interval Data Type

You cannot use the INTERVAL data type for columns in Amazon Redshift tables.

Redshift Interval Data Types Examples

Below are some of commonly used INTERVAL data type conversion examples.

Redshift – Add a 2 days to the current date time using interval types

select now();
              now
-------------------------------
 2019-08-26 15:30:22.013369+00
(1 row)

select now() + interval '2 days' as date_plus;
           date_plus
-------------------------------
 2019-08-28 15:30:25.270575+00
(1 row)

Redshift – Add a 12 hours to the current date time using interval types

select now() + interval '12 hours' as date_plus;
           date_plus
-------------------------------
 2019-08-27 03:31:09.718606+00
(1 row)

Redshift – Add a 30 seconds to the current date time using interval types

select now() + interval '30 seconds' as date_plus;
           date_plus
-------------------------------
 2019-08-26 15:32:07.582127+00
(1 row)

Add 2 hours and 30 minutes to the current date time using interval types

select now() + interval '2 hours, 30 minutes' as dateplus;
           date_plus
-------------------------------
 2019-08-26 18:02:08.822373+00
(1 row)

Hope this helps 🙂