Migrate date functions from Oracle to Amazon RDS for PostgreSQL

PostgreSQL is considered one of the top database choices when customers migrate from commercial databases such as Oracle or SQL Server. AWS provides two managed PostgreSQL options: Amazon Relational Database Service (Amazon RDS) for PostgreSQL and Amazon Aurora PostgreSQL-Compatible Edition.

Moving from Oracle to Amazon RDS for PostgreSQL or Amazon Aurora PostgreSQL-Compatible Edition can require updates to the database schema or SQL commands used to access the data. To assist migrations, AWS provides the AWS Schema Conversion Tool (AWS SCT) to convert application-embedded SQL code and offers a migration playbook that documents a number of functional mappings between Oracle database and PostgreSQL.

While the tooling helps to automate the migration effort, there are scenarios where a developer needs to intervene and manually convert SQL from Oracle to PostgreSQL. The date and timestamp columns are significantly used in many applications to track the time element of the data. As we migrate from one database to another we need to maintain symmetry between the converted date and time values so that dependent components are not impacted. In this post, we show the difference between Oracle and PostgreSQL date and timestamp related functions and how to convert them to make their result equivalent.

Date-related functions in Oracle and PostgreSQL

Oracle’s DATE data type is equivalent to TIMESTAMP WITHOUT TIME ZONE in PostgreSQL. For demonstration purposes, in the queries of this post we use the CREATE_DATE column of DATE data type in Oracle and TIMESTAMP WITHOUT TIME ZONE in PostgreSQL. The format used in database client setting for Oracle and PostgreSQL is YYYY-MM-DD HH:MI:SS.SSS. The code snippets shown in this post are tested in Oracle version 19.0 and PostgreSQL version 13 and later.

Oracle SYSDATE and PostgreSQL NOW() and CURRENT_DATE

In Oracle, SYSDATE is used to get the system current date and time, and the format depends on the value of the NLS_DATE_FORMAT initialization parameter.

PostgreSQL has a built-in NOW()function which returns current date and time till microseconds with time zone, whereas another PostgreSQL function CURRENT_DATE gives only the current date. The format of the DATE and TIMESTAMP depends on the database parameter or database client settings. Both of these functions are not equivalent to SYSDATE function of Oracle. Oracle’s SYSDATE output is of DATE data type whereas PostgreSQL NOW() is of TIMESTAMP datatype and CURRENT_DATE is of DATE data type. For more information, refer to Oracle and PostgreSQL system dates and timestamps are different.

The following table shows the difference between Oracle’s SYSDATE and PostgreSQL CURRENT_DATE.

SELECT SYSDATE FROM DUAL;
SYSDATE
-------------------
2023-07-07 09:18:03.000

SELECT CURRENT_DATE;
current_date
-------------------
2023-07-07

SELECT NOW();
current_date
-------------------
2023-07-07 09:23:45.729985+02

You can cast PostgreSQL’s CURRENT_TIMESTAMP to TIMESTAMP WITHOUT TIME ZONE to get a similar result as Oracle’s SYSDATE.

SELECT SYSDATE FROM DUAL;
SYSDATE
-------------------
2023-07-07 09:23:45.000

SELECT CURRENT_TIMESTAMP::TIMESTAMP WITHOUT TIME ZONE;
current_timestamp
-------------------
2023-07-07 09:23:45.729

TRUNC in Oracle and DATE_TRUNC in PostgreSQL

In Oracle, you can use TRUNC with the DATE data type to truncate the date to a specific level of precision. In PostgreSQL, you can use CAST or DATE_TRUNC to truncate the date or timestamp column. CAST is used to convert one data type to another and DATE_TRUNC is used to truncate TIMESTAMP or INTERVAL column.

SELECT TRUNC(SYSDATE) AS "DATE_ORACLE"
FROM DUAL;
DATE_ORACLE
-------------------
2023-07-06 00:00:00.000

SELECT CAST(CURRENT_DATE AS TIMESTAMP) 
AS "DATE_POSTGRESQL";
DATE_POSTGRESQL
-------------------
2023-07-06 00:00:00.000

SELECT date_trunc('day', CURRENT_DATE)::timestamp AS "DATE_POSTGRESQL";
DATE_POSTGRESQL
-------------------
2023-07-06 00:00:00.000

TRUNC BY DAY/MONTH/YEAR in Oracle and PostgreSQL

The truncate function with DATE or TIMESTAMP datatype column is used to round transaction timestamps to desired granularity. For reporting application this function helps to create time based aggregation like daily, weekly or monthly summaries of data in Oracle and PostgreSQL database.

The TRUNC (<DateField>, <fmt>) (date) function in Oracle returns DATE with the time portion of the day truncated to the unit specified for format model (fmt): ’DD’/’MONTH’/’YEAR’. The value returned is always of data type DATE. Similar functionality can be achieved using DATE_TRUNC() function in PostgreSQL. The date_trunc() is used to truncate a DATE, TIME, or TIMESTAMP to the specified precision.

The TRUNC (<DateField>,’DD’) function in Oracle and DATE_TRUNC(‘DAY’, <DateField>) function in PostgreSQL truncate the date column to day ‘YYYY-MM-DD’ and return time portion as ‘00:00:00.000’. In the following example, the CREATE_DATE column is of the DATE data type in Oracle and TIMESTAMP WITHOUT TIME ZONE data type in PostgreSQL. Oracle’s TRUNC function with argument ‘DD’ truncates the date column’s time portion from 12:53:19.000 to 00:00:00.000. It can be converted in PostgreSQL using DATE_TRUNC with ‘DAY’ as an argument.

The TRUNC(<DateField>,’MONTH’) function in Oracle and DATE_TRUNC(‘MONTH’, <DateField>) function in PostgreSQL truncate the date column to month part. PostgreSQL truncate the date column to day ‘YYYY-MM-DD’ and exclude time portion. In the following example, Oracle’s TRUNC function with argument ‘MONTH’ truncates the date column from the twenty-first day of the month to the first day of the month. It can be converted in PostgreSQL using DATE_TRUNC with ‘MONTH’ as an argument.

The TRUNC(<DateField>,’YEAR’) function in Oracle and DATE_TRUNC(‘YEAR’, <DateField>) function in PostgreSQL truncate the date column to year part.

In the following example, Oracle’s TRUNC function with argument ‘YEAR’ truncates the date column to the first day of the year. It can be converted in PostgreSQL using DATE_TRUNC with ‘YEAR’ as an argument.

SELECT CREATE_DATE as "CREATE_DATE",  
TRUNC(CREATE_DATE,'DD') AS "TRUNC_BY_DAY"
FROM TAB_DATE;
CREATE_DATE            |    TRUNC_BY_DAY 
----------------------- ------------------
2018-03-21 12:53:19.000| 2018-03-21 00:00:00.000

SELECT CREATE_DATE as "CREATE_DATE", 
DATE_TRUNC('DAY',CREATE_DATE) AS "TRUNC_BY_DAY"
FROM TAB_DATE;
CREATE_DATE            |    TRUNC_BY_DAY 
----------------------- ------------------
2018-03-21 12:53:19.000| 2018-03-21 00:00:00.000

SELECT CREATE_DATE as "CREATE_DATE",  
TRUNC(CREATE_DATE,'MONTH') AS "TRUNC_BY_MONTH”
FROM TAB_DATE;
CREATE_DATE            |    TRUNC_BY_MONTH
----------------------- ------------------
2018-03-21 12:53:19.000| 2018-03-01 00:00:00.000

SELECT CREATE_DATE as "CREATE_DATE",  
DATE_TRUNC('MONTH',CREATE_DATE) AS "TRUNC_BY_MONTH”
FROM TAB_DATE;
CREATE_DATE           |    TRUNC_BY_MONTH
----------------------- ----------------------
2018-03-21 12:53:19.000| 2018-03-01 00:00:00.000

SELECT CREATE_DATE as "CREATE_DATE",  
TRUNC(CREATE_DATE,YEAR) AS "TRUNC_BY_YEAR”
FROM TAB_DATE;
CREATE_DATE            |    TRUNC_BY_YEAR  
----------------------- ------------------
2018-03-21 12:53:19.000| 2018-01-01 00:00:00.000

SELECT CREATE_DATE as "CREATE_DATE",  
DATE_TRUNC('YEAR', CREATE_DATE) AS "TRUNC_BY_YEAR”
FROM TAB_DATE;
CREATE_DATE           |    TRUNC_BY_YEAR
----------------------- -------------------
2018-03-21 12:53:19.000| 2018-01-01 00:00:00.000

TO_DATE in Oracle and PostgreSQL

The TO_DATE function is used to convert the character data type to date. It gives the result in a different format in Oracle and PostgreSQL. In Oracle the result is in ‘YYYY-MM-DD 00:00:00.000’ format, whereas in PostgreSQL it is in this format ‘YYYY-MM-DD’. Therefore explicit typecasting is required.

The following table illustrates using TO_DATE with format 'YYYY-MM-DD' in Oracle and PostgreSQL and the difference in the result.

SELECT TO_DATE('2023-06-26', 'YYYY-MM-DD')
AS DATE_ORACLE FROM DUAL;
DATE_ORACLE           
------------------
2023-06-26 00:00:00.000

SELECT TO_DATE('2023-06-26', 'YYYY-MM-DD') 
AS “DATE_POSTGRESQL”;
DATE_POSTGRESQL
------------------
2023-06-26

To convert TO_DATE(<DATE_FIELD>, 'YYYY-MM-DD') from Oracle to the equivalent in PostgreSQL, typecasting is required: TO_DATE(<DATE_FIELD>, 'YYYY-MM-DD')::TIMESTAMP(0) or TO_DATE(<DATE_FIELD>, 'YYYY-MM-DD'::TIMESTAMP WITHOUT TIME ZONE).

In the following example typecasting of TO_DATE(<DATE_FIELD>, 'YYYY-MM-DD') to TIMESTAMP(0) is done in PostgreSQL.

SELECT
TO_DATE('2023-06-26', 'YYYY-MM-DD')
AS DATE_ORACLE FROM DUAL;
DATE_ORACLE           
------------------
2023-06-26 00:00:00.000

SELECT
TO_DATE('2023-06-26', 'YYYY-MM-DD') ::TIMESTAMP(0)
AS “DATE_POSTGRESQL”;
DATE_POSTGRESQL           
------------------
2023-06-26 00:00:00.000

In the following example typecasting of TO_DATE(<DATE_FIELD>, 'YYYY-MM-DD') to TIMESTAMP WITHOUT TIME ZONE is done in PostgreSQL.

SELECT
TO_DATE('2023-06-26', 'YYYY-MM-DD')
AS DATE_ORACLE FROM DUAL;
DATE_ORACLE           
------------------
2023-06-26 00:00:00.000

SELECT
TO_DATE('2023-06-26', 'YYYY-MM-DD') ::TIMESTAMP WITHOUT TIME ZONE
AS “DATE_POSTGRESQL”;
DATE_POSTGRESQL           
------------------
2023-06-26 00:00:00.000

The following table illustrates using TO_DATE with format 'YYYY-MM-DD HH24:MI:SS'.

The TO_DATE function in Oracle and PostgreSQL gives different results. Oracle returns the result in ‘YYYY-MM-DD HH24:MI:SS' format, whereas PostgreSQL returns it in ‘YYYY-MM-DD’.

SELECT
TO_DATE('2023-06-26 13:15:53', 'YYYY-MM-DD HH24:MI:SS')
AS DATE_ORACLE FROM DUAL;
DATE_ORACLE           
------------------
2023-06-26 13:15:53.000

SELECT
TO_DATE('2023-06-26 13:15:53', 'YYYY-MM-DD HH24:MI:SS')
AS “DATE_POSTGRESQL”;
DATE_POSTGRESQL           
------------------
2023-06-26

To convert the TO_DATE(<DATE_FIELD>, 'YYYY-MM-DD HH24:MI:SS') function in Oracle to PostgreSQL, TO_TIMESTAMP is required, along with typecasting: TO_TIMESTAMP(<Date_Field>, ‘YYYY-MM-DD HH24:MI:SS’)::TIMESTAMP(0) or TO_TIMESTAMP(<Date_Field>, ‘YYYY-MM-DD HH24:MI:SS’)::TIMESTAMP WITHOUT TIME ZONE.

In the following example typecasting of TO_DATE(<DATE_FIELD>, 'YYYY-MM-DD') to TIMESTAMP(0) is done in PostgreSQL.

SELECT
TO_DATE('2023-06-26 13:15:53', 'YYYY-MM-DD HH24:MI:SS')
AS DATE_ORACLE FROM DUAL;
DATE_ORACLE           
------------------
2023-06-26 13:15:53.000

SELECT
TO_TIMESTAMP('2023-06-26 13:15:53', 'YYYY-MM-DD HH24:MI:SS')::TIMESTAMP(0)
AS “DATE_POSTGRESQL”;
DATE_POSTGRESQL           
------------------
2023-06-26 13:15:53.000

In the following example typecasting of TO_DATE(<DATE_FIELD>, 'YYYY-MM-DD') to TIMESTAMP WITHOUT TIME ZONE is done in PostgreSQL.

SELECT
TO_DATE('2023-06-26 13:15:53',
'YYYY-MM-DD HH24:MI:SS')
AS DATE_ORACLE FROM DUAL;
DATE_ORACLE           
------------------
2023-06-26 13:15:53.000

SELECT
TO_TIMESTAMP('2023-06-26 13:15:53', 
'YYYY-MM-DD HH24:MI:SS')::TIMESTAMP WITHOUT TIME ZONE
AS “DATE_POSTGRESQL”;
DATE_POSTGRESQL           
------------------
2023-06-26 13:15:53.000

Add INTEGER to DATE data type in Oracle and PostgreSQL

In scenarios where the number of days are added to a DATE column, result matches in both Oracle and PostgreSQL. However, when number of days are added to a TIMESTAMP in PostgreSQL it gives an error.

To convert such queries from Oracle to PostgreSQL, INTERVAL should be added to a timestamp column.

SELECT CREATE_DATE AS "CREATE_DATE", 
CREATE_DATE + 2 AS "NEW_DATE"
FROM TAB_DATE;
CREATE_DATE            |    NEW_DATE
----------------------- --------------------
2018-03-21 12:53:19.000| 2018-03-23 12:53:19.000

SELECT CREATE_DATE AS "CREATE_DATE", 
CREATE_DATE + INTERVAL '2' DAY AS "NEW_DATE" 
FROM TAB_DATE;
CREATE_DATE            |    NEW_DATE
----------------------- --------------------
2018-03-21 12:53:19.000| 2018-03-23 12:53:19.000

PostgreSQL doesn’t allow an integer to be added to a TIMESTAMP column. It gives an error as shown below. To resolve it, add INTERVAL to a TIMESTAMP column.

SELECT CREATE_DATE AS "CREATE_DATE", CREATE_DATE + 2 AS "NEW_DATE"
FROM TAB_DATE;

----------------------------------------------------------------------------------
SQL Error [42883]: ERROR: operator does not exist: timestamp without time zone + integer
  Hint: No operator matches the given name and argument types. You might need to add explicit type casts.
----------------------------------------------------------------------------------

Add an INTEGER column to the DATE data type column in Oracle and PostgreSQL

Sometimes number of days can be stored in database as INTEGER column and there can be arithmetic operation between DATE column and an INTEGER column. In Oracle INTEGER column can be added to a DATE column but in PostgreSQL to add INTEGER column to a TIMESTAMP column the INTEGER column must be converted into INTERVAL and then added to a TIMESTAMP column.

In this example, we multiply the INTEGER column with ‘1’ DAY INTERVAL and then add this interval value to a DATE or TIMESTAMP WITHOUT TIME ZONE column.

SELECT CREATE_DATE AS " DATE", 
CREATE_DATE + type_id AS "NEW_DATE"
FROM TAB_DATE;
DATE                   |    NEW_DATE
----------------------- --------------------
2018-01-10 14:04:44.000| 2018-01-11 14:04:44.000

SELECT CREATE_DATE AS "DATE", 
CREATE_DATE + INTERVAL '1' day * type_id AS "NEW_DATE"
FROM TAB_DATE;
DATE                   |    NEW_DATE
----------------------- --------------------
2018-01-10 14:04:44.000| 2018-01-11 14:04:44.000

If you don’t follow the previous steps, you will get an error because PostgreSQL doesn’t allow an integer to be added to a DATE or TIMESTAMP column.

SELECT CREATE_DATE AS " DATE", 
CREATE_DATE + type_id AS "NEW_DATE"
FROM TAB_DATE;

----------------------------------------------------------------------------------
SQL Error [42883]: ERROR: operator does not exist: timestamp without time zone + numeric
  Hint: No operator matches the given name and argument types. You might need to add explicit type casts.
----------------------------------------------------------------------------------

Similarly, it gives an error while adding a DATE column with the NUMERIC column.

SELECT CREATE_DATE AS "DATE", 
CREATE_DATE::date + type_id AS "NEW_DATE"
FROM TAB_DATE;

----------------------------------------------------------------------------------
SQL Error [42883]: ERROR: operator does not exist: date + numeric
  Hint: No operator matches the given name and argument types. You might need to add explicit type casts.
----------------------------------------------------------------------------------

Date difference in Oracle and PostgreSQL

When two date columns are subtracted in Oracle, the result is in NUMBER, whereas it’s INTERVAL in PostgreSQL for timestamp columns.

SELECT EXPIRY_DATE, CREATE_DATE , 
EXPIRY_DATE - CREATE_DATE AS "DATE_DIFF"
FROM TAB_DATE; 
EXPIRY_DATE            |    CREATE_DATE   | DATE_DIFF        
----------------------  --------------------
2018-01-11 23:59:00.000| 2018-01-10 14:04:44.000| 1.4126851852

SELECT EXPIRY_DATE, CREATE_DATE , 
EXPIRY_DATE - CREATE_DATE AS "DATE_DIFF"
FROM TAB_DATE;
EXPIRY_DATE            |    CREATE_DATE   | DATE_DIFF        
----------------------- -------------------
2018-01-11 23:59:00.000| 2018-01-10 14:04:44.000| 1 day 09:54:16

In PostgreSQL, to calculate the date difference in days where date datatype is timestamp and related datatype, we can use the EXTRACT with epoch function to get the difference between two dates in numeric. Also, EXTRACT can be used with date of interval datatype, convert it into seconds, and then divide by total number of seconds in a day, which is 24*60*60.

EXTRACT (EPOCH FROM DateTime);

SELECT EXPIRY_DATE, CREATE_DATE ,
EXPIRY_DATE - CREATE_DATE AS “DATE_DIFF”
FROM TAB_DATE;
EXPIRY_DATE            |    CREATE_DATE   | DATE_DIFF        
----------------------- -------------------
2018-01-11 23:59:00.000| 2018-01-10 14:04:44.000| 1.4126851852

SELECT EXPIRY_DATE AS “EXPIRY_DATE”, CREATE_DATE as “CREATE_DATE”,
(EXTRACT (DAY FROM (expiry_date-create_date))*24*60*60+
EXTRACT (HOUR FROM (expiry_date-create_date))*60*60+
EXTRACT (MINUTE FROM (expiry_date-create_date))*60+
EXTRACT (SECOND FROM (expiry_date-create_date)))/(24*60*60) AS “DATE_DIFF_INT”
FROM TAB_DATE;
EXPIRY_DATE            |    CREATE_DATE   | DATE_DIFF_INT        
----------------------- -------------------
2018-01-11 23:59:00.000| 2018-01-10 14:04:44.000| 1.4126851852

SELECT EXPIRY_DATE, CREATE_DATE ,
EXPIRY_DATE - CREATE_DATE AS “DATE_DIFF”
FROM TAB_DATE;
EXPIRY_DATE            |    CREATE_DATE   | DATE_DIFF        
----------------  ---------------  -----------
2018-01-11 23:59:00.000| 2018-01-10 14:04:44.000| 1.4126851852

SELECT EXPIRY_DATE , CREATE_DATE, 
EXTRACT('epoch' FROM EXPIRY_DATE - CREATE_DATE)/ (24*60*60) 
AS “DATE_DIFF_INT” from TAB_DATE;
EXPIRY_DATE            |    CREATE_DATE   | DATE_DIFF_INT        
---------------- --------------- -----------
2018-01-11 23:59:00.000| 2018-01-10 14:04:44.000| 1.4126851851851852

Date difference using TRUNC in Oracle

In Oracle, when TRUNC is used with date columns to get the date difference, the result is INTEGER value. This can be achieved in PostgreSQL using the CAST or DATE_PART function.

SELECT TRUNC(SYSDATE+93)- TRUNC(SYSDATE)
AS “DATE_DIFF_IN_DAY”
FROM DUAL;
DATE_DIFF_IN_DAY          
-----------------
93

SELECT (CURRENT_DATE +93) - CURRENT_DATE
AS “DATE_DIFF_IN_DAY”;
DATE_DIFF_IN_DAY          
-----------------
93

SELECT EXPIRY_DATE, 
CREATE_DATE , 
TRUNC(EXPIRY_DATE) - TRUNC(CREATE_DATE) AS "DATE_DIFF"
FROM TAB_DATE;
EXPIRY_DATE            |    CREATE_DATE   | DATE_DIFF        
---------------- --------------- -----------
2018-01-11 23:59:00.000| 2018-01-10 14:04:44.000
| 1

SELECT EXPIRY_DATE AS "EXPIRY_DATE", 
CREATE_DATE AS "CREATE_DATE", 
DATE_PART('DAY',EXPIRY_DATE - CREATE_DATE) AS "DATE_DIFF"
FROM TAB_DATE;
EXPIRY_DATE            |    CREATE_DATE   | DATE_DIFF        
---------------- --------------- -----------
2018-01-11 23:59:00.000| 2018-01-10 14:04:44.000
| 1

Date difference in months in Oracle and PostgreSQL

In Oracle, MONTHS_BETWEEN gives the date difference in NUMBER, whereas in PostgreSQL, the AGE function gives the date difference in INTERVAL.

SELECT MONTHS_BETWEEN(SYSDATE+93,SYSDATE)  
AS “MNTH_BETWEEN” FROM DUAL;
MNTH_BETWEEN          
-----------------
3.0322580645

SELECT AGE(CURRENT_DATE + 93, CURRENT_DATE) 
AS “MNTH_BETWEEN”;
MNTH_BETWEEN          
-----------------
3 mons 1 day

The MONTHS_BETWEEN function along with TRUNC gives the result INTEGER. This can be converted in PostgreSQL using DATE_PART and the AGE function.

SELECT 
TRUNC(MONTHS_BETWEEN(SYSDATE+93,SYSDATE)) 
AS AGE FROM DUAL;
AGE
--------
3

SELECT 
DATE_PART('MONTH',AGE(CURRENT_DATE + 93, CURRENT_DATE)) 
as “AGE”;
AGE
--------
3

In PostgreSQL, you can use AGE with the EXTRACT function to match Oracle’s MONTHS_BETWEEN result. It converts the AGE function’s output from INTERVAL to NUMERIC.

SELECT
MONTHS_BETWEEN (SYSDATE+93,SYSDATE)
AS “NUM_OF_MONTHS”
FROM DUAL;
NUM_OF_MONTHS 
-----------------
3.0322580645

SELECT
(EXTRACT(YEAR from AGE(CURRENT_DATE + 93, CURRENT_DATE))*12
+ EXTRACT(MONTH from AGE(CURRENT_DATE + 93, CURRENT_DATE))
+ (EXTRACT(DAY from AGE(CURRENT_DATE + 93, CURRENT_DATE))/31
))as “NUM_OF_MONTHS”;
NUM_OF_MONTHS 
-----------------
3.0322580645

Convert the NUMTODSINTERVAL function in Oracle to PostgreSQL

In Oracle NUMTODSINTERVAL function is used to convert a NUMBER value to an INTERVAL. It converts a given NUMBER value to an INTERVAL DAY TO SECOND literal. In NUMTODSINTERVAL(n, 'interval_unit'), the n argument is the input value and ‘interval_unit‘ should be one of the following value: ‘DAY’, ‘HOUR’, ‘MINUTE’, or ‘SECOND’.

For example:

• NUMTODSINTERVAL(90, ‘SECOND’) changes 90 seconds into 0 0:1:30.0, which is equal to 1 minute and 30 seconds.
• NUMTODSINTERVAL(90, ‘MINUTE’) changes 90 minutes into 0 1:30:0.0, which is equal to 1 hour and 30 minutes.
• NUMTODSINTERVAL(90, ‘HOUR’) changes 90 hours into 3 18:0:0.0, which is equal to 3 days and 18 hours.

SELECT 
NUMTODSINTERVAL(90, 'SECOND') AS "SEC_INTERVAL" ,
NUMTODSINTERVAL(90, 'MINUTE') AS "MIN_INTERVAL",
NUMTODSINTERVAL(90, 'HOUR') AS "HOUR_INTERVAL"
FROM DUAL;

SEC_INTERVAL |    MIN_INTERVAL | HOUR_INTERVAL 
-------------- ------------------- ------------
0 0:1:30.0    | 0 1:30:0.0        |  3 18:0:0.0

In the following example we will convert a number value to an INTERVAL DAY TO SECOND literal.

SELECT CREATE_DATE AS “DATE”, 
CREATE_DATE + NUMTODSINTERVAL(90, 'SECOND') AS “SEC_INTERVAL_DATE”,
CREATE_DATE + NUMTODSINTERVAL(65, 'MINUTE') AS “MIN_INTERVAL_DATE”,
CREATE_DATE + NUMTODSINTERVAL(25, 'HOUR') AS “HOUR_INTERVAL_DATE”	
FROM TAB_DATE;
DATE                   |SEC_INTERVAL_DATE               
|  MIN_INTERVAL_DATE   | HOUR_INTERVAL_DATE
----------------------  -------------------
-------------  -----------------------  ----
2018-03-21 12:53:19.000 | 2018-03-21 12:54:49.000 
| 2018-03-21 13:58:19.000 | 2018-03-22 13:53:19.000

SELECT CREATE_DATE AS “DATE”,
CREATE_DATE + INTERVAL '90' SECOND AS “SEC_INTERVAL_DATE”,
CREATE_DATE + INTERVAL '65' MINUTE AS “MIN_INTERVAL_DATE”,
CREATE_DATE + INTERVAL '25' HOUR AS “HOUR_INTERVAL_DATE”
FROM TAB_DATE;
DATE                    |    SEC_INTERVAL_DATE   
|  MIN_INTERVAL_DATE    | HOUR_INTERVAL_DATE
-----------------------  ----------------------------------  
------------------------  ---- 
2018-03-21 12:53:19.000 | 2018-03-21 12:54:49.000 
| 2018-03-21 13:58:19.000 |  2018-03-22 13:53:19.000

Convert ADD_MONTHS in ORACLE to PostgreSQL

SELECT CREATE_DATE AS “DATE”,
ADD_MONTHS(CREATE_DATE,4) AS “NEW_DATE”
FROM TAB_DATE;
DATE                   |      NEW_DATE 
----------------------- -----------------
2018-03-21 12:53:19.000| 2018-07-21 12:53:19.000

SELECT CREATE_DATE AS “DATE”,
CREATE_DATE + INTERVAL '4' MONTH AS “NEW_DATE”
FROM TAB_DATE;
DATE                   |      NEW_DATE 
----------------------- -----------------
2018-03-21 12:53:19.000| 2018-07-21 12:53:19.000

Convert date series in Oracle to PostgreSQL

In Oracle, you can use CONNECT BY LEVEL to generate series. PostgreSQL has the GENERATE_SERIES function, which allows you to generate a set of data starting at one point, ending at another point, and optionally set the incrementing value.

SELECT 
( TO_DATE('2023/07/01', 'YYYY/MM/DD') + LEVEL - 1) 
“DATE”, 
LEVEL
FROM DUAL CONNECT BY
LEVEL <= 10;
       DATE          | LEVEL 
---------------------+-------
 2023-07-01 00:00:00 |     1
 2023-07-02 00:00:00 |     2
 2023-07-03 00:00:00 |     3
 2023-07-04 00:00:00 |     4
 2023-07-05 00:00:00 |     5
 2023-07-06 00:00:00 |     6
 2023-07-07 00:00:00 |     7
 2023-07-08 00:00:00 |     8
 2023-07-09 00:00:00 |     9
 2023-07-10 00:00:00 |    10

SELECT 
"DATE", 
LEVEL 
FROM GENERATE_SERIES ('2023-07-01'::TIMESTAMP, 
'2023-07-10'::TIMESTAMP, INTERVAL '1 DAY') WITH ORDINALITY 
AS T ("DATE",LEVEL);
       DATE          | LEVEL 
---------------------+-------
 2023-07-01 00:00:00 |     1
 2023-07-02 00:00:00 |     2
 2023-07-03 00:00:00 |     3
 2023-07-04 00:00:00 |     4
 2023-07-05 00:00:00 |     5
 2023-07-06 00:00:00 |     6
 2023-07-07 00:00:00 |     7
 2023-07-08 00:00:00 |     8
 2023-07-09 00:00:00 |     9
 2023-07-10 00:00:00 |    10

Conclusion

In this post, we showed you how to handle the most common date related migration issues when moving from Oracle to Amazon RDS for PostgreSQL or Amazon Aurora PostgreSQL-Compatible Edition. For more information about AWS database migration, refer to AWS DMS and AWS SCT. You can also refer to Migration tips for developers for Oracle or Microsoft SQL Server to PostgreSQL and Migration playbook Oracle to Aurora PostgreSQL.

We invite you to leave your feedback in the comments sections.

About the authors

Neha Sharma is working as database specialist with Amazon Web Services. She enables AWS customers to migrate their databases to AWS Cloud. Besides work, she likes to be actively involved in various sports activities and like’s to socialize with people.

Sweta Krishna is a Database Migration Specialist with the Professional Services team at Amazon Web Services. She works closely with the customers to help them migrate and modernize their database solutions to AWS.