INTERVAL Data Type - PostgreSQL to Oracle Migration

In PostgreSQL, the INTERVAL data type stores date and time interval. In Oracle, you have to use either INTERVAL YEAR TO MONTH or INTERVAL DAY TO SECOND data type.

Note that PostgreSQL supports different formats of interval literals, and in most cases you need to change them for Oracle.

PostgreSQL:

  -- A sample table
  CREATE TABLE shipment
  (
     name VARCHAR(70),
     ordered TIMESTAMP,
     delivery INTERVAL
  );
 
  -- Specify an interval as string literal
  INSERT INTO shipment VALUES ('Orange', NOW(), '3 days 4 hours'); 
 
  -- Define the delivery date
  SELECT ordered, ordered + delivery FROM shipment;
  # 2013-02-21 19:23:25.751 | 2013-02-24 23:23:25.751

Oracle:

  -- A sample table
  CREATE TABLE shipment
  (
     name2 VARCHAR2(70),
     ordered TIMESTAMP,
     delivery INTERVAL DAY(3) TO SECOND
  );
 
  -- Specify an interval as string literal
  INSERT INTO shipment VALUES ('Orange', SYSTIMESTAMP, '3 4:0:0'); 
 
  -- Define the delivery date
  SELECT ordered, ordered + delivery FROM shipment;
  # 2013-02-21 19:23:25.751 | 2013-02-24 23:23:25.751

• SQLines - Database and SQL Conversion Tool
• SQLines Data - Data Transfer and Database Schema Migration Tool
• Free 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.