Greenplum Interval Data Type and Conversion Examples

  • Post author:
  • Post last modified:May 10, 2019
  • Post category:Greenplum
  • Reading time:4 mins read

An internal data types in Greenplum are associated with time span. The interval data type allows you to store and manipulate a period of time in years, months, days, hours, minutes, seconds, etc. The interval values are very useful when doing date or time arithmetic. The size of Interval data type in Greenplum are 12 bytes that can store a period with the allowed range is from -178,000,000 years to 178,000,000 years. In this article, we will check Greenplum Interval Data Type.

Greenplum Interval Data Type Syntax

Below is the syntax of interval data types in Greenplum or PostgreSQL:

interval [ (p) ]

An interval value can have an optional precision value p with the permitted range is from 0 to 6. The precision p is the number of fraction digits retained in the second fields if you are using it in calculations.

Also Read:

Greenplum Interval Data Types Implementation

PostgreSQL implements the INTERVAL data type in a different way compared to other databases.

  • Internally, PostgreSQL stores interval values as months, days, and seconds. The months and days values are integers while the seconds can field can have fractions.
  • It is not possible to specify the scale of an interval data type. The precision of Greenplum interval data types include fraction of seconds with up to 6 significant digits.
  • In PostgreSQL, interval literals must include the units like ‘100 days 10 hours 50 minutes’

Greenplum Interval Data Types Conversion Examples

Add a one day to current date time using interval data types:

template1=# select now();
               now
----------------------------------
 2019-05-10 15:19:53.238863+05:30
(1 row)

template1=# select now()+ interval '1 day';
             ?column?
----------------------------------
 2019-05-11 15:19:59.830838+05:30
(1 row)

Add 20 minutes to current date time using interval data types:

template1=#  select now()+ interval '20 minutes';
             ?column?
----------------------------------
 2019-05-10 15:41:44.133786+05:30
(1 row)

Add 12 hours to current date time using interval data types:

template1=# select now()+ interval '12 hour';
             ?column?
----------------------------------
 2019-05-11 03:22:49.356934+05:30
(1 row)

Add 10 seconds to current date time using interval data types:

template1=# select now()+ interval '10 seconds';
             ?column?
----------------------------------
 2019-05-10 15:24:11.740531+05:30
(1 row)

Convert PostgreSQL or Greenplum Interval Data types to Date format: 

SELECT cast(extract(year FROM interval '2019 year 5 months 10 days 10 minutes 10 seconds') 
              || '-' 
              || extract(month FROM interval '2019 year 5 months 10 days 10 minutes 10 seconds') 
              || '-' 
              || extract(day FROM interval '2019 year 5 months 10 days 10 minutes 10 seconds') AS date );
			  
    date
------------
 2019-05-10
(1 row)

Hope this helps 🙂

Tags: