Get Interval In Months - PostgreSQL to Oracle Migration

In PostgreSQL, you can use EXTRACT and AGE functions to get the interval between 2 timestamps in months.

PostgreSQL:

  -- AGE function returns year-month-day interval between 2 timestamps
  SELECT AGE(TIMESTAMP '2013-03-11 00:00:00', TIMESTAMP '2010-09-17 00:00:00');
  # 2 years 5 mons 24 days

Now you can extract years, multiple by 12 and extract months to get the interval in months:

  -- Get interval in months
  SELECT EXTRACT(YEAR FROM AGE(TIMESTAMP '2013-03-11', TIMESTAMP '2010-09-17')) * 12 +
              EXTRACT(MONTH FROM AGE(TIMESTAMP '2013-03-11', TIMESTAMP '2010-09-17'));
  # 29

In Oracle you can use datetime arithmetic subtraction operator - instead of AGE function to get the interval between 2 timestamps:

Oracle:

  -- Subtraction operator - returns interval (DAY TO SECOND by default)
  SELECT TIMESTAMP '2013-03-11 00:00:00' - TIMESTAMP '2010-09-17 00:00:00' FROM dual;
  # +000000906 00:00:00.000000000
 
  -- Convert to YEAR TO MONTH interval
  SELECT (TIMESTAMP '2013-03-11 00:00:00' - TIMESTAMP '2010-09-17 00:00:00') YEAR TO MONTH FROM dual;
  # +02-06

Note that the interval was rounded to 2 years 6 months when converted to YEAR TO MONTH interval in Oracle, while the actual interval is 2 years, 5 months and 24 days.

Using EXTRACT function you can get interval between 2 timestamps in months:

  -- Get interval in months
  SELECT EXTRACT(YEAR FROM (TIMESTAMP '2013-03-11 00:00:00' - TIMESTAMP '2010-09-17 00:00:00') 
                     YEAR TO MONTH) * 12 +
              EXTRACT(MONTH FROM (TIMESTAMP '2013-03-11 00:00:00' - TIMESTAMP '2010-09-17 00:00:00') 
                     YEAR TO MONTH) 
  FROM dual;
  # 30

You can see that using EXTRACT in PostgreSQL and Oracle brings different results as Oracle rounds the month part.

Oracle provides the built-in function MONTHS_BETWEEN to get the number of months between 2 timestamp and date values:

Oracle:

  -- MONTHS_BETWEEN returns a decimal number
  SELECT MONTHS_BETWEEN(TIMESTAMP '2013-03-11 00:00:00', TIMESTAMP '2010-09-17 00:00:00') 
  FROM dual;
  # 29.8064516
 
  -- Use FLOOR function to get the same result as PostgreSQL
  SELECT FLOOR(MONTHS_BETWEEN(TIMESTAMP '2013-03-11 00:00:00', TIMESTAMP '2010-09-17 00:00:00')) 
  FROM dual;
  # 29

• SQLines - Database and SQL Conversion Tool
• SQLines Data - Data Transfer and Database Schema Migration Tool
• Online SQL Conversion Tool

• PostgreSQL to Oracle Migration Reference

SQLines offers services and tools to help you migrate databases and applications. For more information, please contact us at support@sqlines.com.

Written by Dmitry Tolpeko - March 2013.