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.
Oracle
PostgreSQL
You can cast PostgreSQL’s CURRENT_TIMESTAMP to TIMESTAMP WITHOUT TIME ZONE to get a similar result as Oracle’s SYSDATE.
Oracle
PostgreSQL
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.
Oracle
PostgreSQL
Function: TRUNC (DATE)
Function: CAST , DATE_TRUNC
The TRUNC function is used to get the date with the time portion of the day truncated to a specific unit of measure: TRUNC(<date field> [, datepart ] )
The date_part includes DD, MM, YYYY, HH, MI and so on.
CAST: A cast specifies how to perform a conversion between two data types. Here, a cast is used to convert the date data type to a timestamp without a time zone.
DATE_TRUNC: Extracts a TIMESTAMP or INTERVAL column and truncates to a specific level of precision: DATE_TRUNC(date_part, Field)
The date_part includes day, month, year, hour, minute, and so on.
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.
Oracle
PostgreSQL
Function:
TRUNC for DAY
TRUNC for MONTH
TRUNC for YEAR
Function:
DATE_TRUNC for DAY
DATE_TRUNC for MONTH
DATE_TRUNC for YEAR
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.
Oracle
PostgreSQL
Function: TO_DATE
Function: TO_DATE
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.
Oracle
PostgreSQL
In the following example typecasting of TO_DATE(<DATE_FIELD>, ‘YYYY-MM-DD’) to TIMESTAMP WITHOUT TIME ZONE is done in PostgreSQL.
Oracle
PostgreSQL
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’.
Oracle
PostgreSQL
Function: TO_DATE(<Date_Field>,’YYYY-MM-DD HH24:MI:SS’)
Function: TO_DATE(<Date_Field>,’YYYY-MM-DD HH24:MI:SS’)
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.
Oracle
PostgreSQL
In the following example typecasting of TO_DATE(<DATE_FIELD>, ‘YYYY-MM-DD’) to TIMESTAMP WITHOUT TIME ZONE is done in PostgreSQL.
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.
Oracle
PostgreSQL
DATE + INTEGER = DATE
TIMESTAMP WITHOUT TIME ZONE + INTERVAL = TIMESTAMP WITHOUT TIME ZONE
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.
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.
Oracle
PostgreSQL
DATE + INTEGER (COLUMN) = DATE
TIMESTAMP WITHOUT TIME ZONE + INTERVAL ‘1’ DAY * INTEGER (COLUMN) = TIMESTAMP WITHOUT TIME ZONE
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.
Similarly, it gives an error while adding a DATE column with the NUMERIC column.
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.
Oracle
PostgreSQL
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.
Oracle
PostgreSQL
Date 1 – Date 2
Function: EXTRACT in PostgreSQL retrieves a field such as a year, month, and day from a date/time value.
Function: EPOCH time represents the total number of seconds elapsed. To extract epoch time from the current or specific time, the EXTRACT() function is used with the EPOCH argument.
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.
Oracle
PostgreSQL
Function: TRUNC
Function: CAST
Oracle
PostgreSQL
Function: TRUNC
Function: DATE_PART is used to retrieve a specific part (like a month, year, hour, minutes) from a date or time:
DATE_PART(field, source)
The field determines which date/time part will be extracted or pulled out from the given source.
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.
Oracle
PostgreSQL
Function: MONTHS_BETWEEN
The MONTHS_BETWEEN() function is used to get the number of months between dates (date1 and date2):
MONTHS_BETWEEN(date1, date2)
Function: AGE
The AGE() function accepts two TIMESTAMP values. It subtracts the second argument from the first one and returns INTERVAL as a result:
AGE(timestamp,timestamp)
The MONTHS_BETWEEN function along with TRUNC gives the result INTEGER. This can be converted in PostgreSQL using DATE_PART and the AGE function.
Oracle
PostgreSQL
Function: MONTHS_BETWEEN
Function: AGE
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.
Oracle
PostgreSQL
Function: MONTHS_BETWEEN
Function: AGE
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.
In the following example we will convert a number value to an INTERVAL DAY TO SECOND literal.
Oracle
PostgreSQL
Function: NUMTODSINTERVAL
90 seconds is converted into 1 minute and 30 seconds using NUMTODSINTERVAL(90,‘SECOND’)
65 minutes is converted into 1 hour and 5 minutes using NUMTODSINTERVAL(65, ‘MINUTE’)
25 hour is converted into 1 day and 1 hour using NUMTODSINTERVAL(25, ‘HOUR’)
Function: INTERVAL
PostgreSQL has INTERVAL literal for adding INTERVAL into DATE.
90 seconds is converted into 1 minute and 30 seconds using INTERVAL ’90’ SECOND.
65 minutes is converted into 1 hour and 5 minutes using INTERVAL ’65’ MINUTE.
25 hour is converted into 1 day and 1 hour using INTERVAL ’25’ HOUR.
Convert ADD_MONTHS in ORACLE to PostgreSQL
Oracle
PostgreSQL
Function: ADD_MONTHS
The ADD_MONTHS() function returns a date with a specified number of months added:
ADD_MONTHS(date1, number_months)
Function: INTERVAL
PostgreSQL allows us to specify intervals when adding to dates:
+ INTERVAL ‘n’ MONTH
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.
Oracle
PostgreSQL
Function: CONNECT BY with LEVEL
Function: GENERATE_SERIES with LEVEL:
generate_series([start], [stop], [{optional}step/interval])
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.
Read MoreAWS Database Blog