Migrations to PostgreSQL always allow us to share interesting information about PostgreSQL compared to databases like Oracle and SQL Server. One of such is related to the behavior of the DATE data type in Oracle vs PostgreSQL and SQL Server vs PostgreSQL. PostgreSQL is a database with a rich set of data types that follow standards. For this reason, we may see some interesting behaviors while migrating from Oracle to PostgreSQL or SQL Server to PostgreSQL. In this article, we shall discuss in detail about DATE data type in Oracle vs PostgreSQL and SQL Server vs PostgreSQL.
DATE data type in Oracle
In Oracle, the DATE data type can store not only the DATE but also the TIME of the day. In order to understand this better, let us consider the following example in which we are creating a table and defining a column with data type as DATE.
CREATE TABLE TESTDATE(A DATE); |
NLS_DATE_FORMAT in Oracle
NLS_DATE_FORMAT in Oracle defines the format to use with TO_CHAR and TO_DATE functions. So each insert to Oracle Table will now depend on the NLS_DATE_FORMAT.
Let us see the current NLS_DATE_FORMAT in our test environment.
select * from nls_session_parameters where parameter = 'NLS_DATE_FORMAT'; |
Let us now insert a record into the Oracle Table and see if it allows us to insert only the DATE in a different format other than NLS_DATE_FORMAT. We shall try both with and without specifying the time. Remember that PostgreSQL may have better flexibility in inserting in different formats which will be discussed later in this article.
INSERT INTO TESTDATE VALUES ('1992-11-12'); |
Now, let us insert some data and see the time of the day along with the date. In the following log, we notice that the data returned contains only the DATE as the NLS_DATE_FORMAT is limited to DATE.
INSERT INTO TESTDATE VALUES (TO_DATE('12/11/2022 10:20:30', 'DD/MM/YYYY HH24:MI:SS')); |
When we use TO_CHAR to also fetch the time along with the DATE, we also see the time i.e. hours, minutes and seconds. This is because it stores both DATE and TIME in Oracle.
This concludes that the DATE data type in Oracle can store both the DATE as well as the TIME of the day.
DATE data type in SQL Server
In SQL Server, we have the DATE data type that works differently when compared to Oracle and similar to PostgreSQL. The DATE data type would only store the date but not the time of the day. In order to store the time of the day along with the date, we have to either use DATETIME or DATETIME2.
Let us see this in action through the following example.
Create table TestDate (col1 Date, col2 Datetime , col3 Datetime2(0)); |
Let us now use the getdate() function in SQL server to insert the date and datetime into the test table we created above.
select getdate(); |
As we see in the following output, the first column with DATE data type only stores the date but not the time. The second column with DATETIME data type stores both day and time of the day including milliseconds. And the third column with DATETIME2(0) limits the number of digits in fractional seconds to 0 digits.
Insert into TestDate values ( getdate(), getdate(), getdate() ); |
Looking to migrate to PostgreSQL ? Need support during the process of Migration ? Contact MigOps today.
DATE data type in PostgreSQL
In PostgreSQL, we have the DATE data type that works similar to SQL Server. DATE data type in PostgreSQL allows us to only store the DATE but not the time of the day.
Let us consider the following example where we are creating a table with the data type for its column set to DATE. Carefully observe Postgres ignoring the TIME being inserted along with the DATE in the following example.
create table _migopstest.a (a date);insert into _migopstest.a values ('05-12-2022');insert into _migopstest.a values ('05-12-2022 02:20:10');select * from _migopstest.a; |
We can also query the data using the TO_CHAR function in PostgreSQL to notice that the time that was inserted using the second Insert statement is being ignored.
select TO_CHAR(a,'YYYY-MM-DD HH24:MI:SS') from _migopstest.a; |
In comparison to Oracle, Postgres supports inserting the date in all reasonable formats. The same may fail or behave differently due to NLS_DATE_FORMAT in Oracle, unless, TO_DATE function is explicitly used. We have the following example in Postgres that shows that the format is not strictly limited.
insert into _migopstest.a values ('January 8, 1999'); |
In SQL Server, we have seen that there are DATETIME and DATETIME2 data types. The equivalent of it would be TIMESTAMP(3) and TIMESTAMP in PostgreSQL. An important point to note is that Postgres limits the fractional seconds to 6 digits whereas SQL Server allows upto 7 digits through DATETIME2 data type. Let us consider these following examples to see it in action.
SQL Server
CREATE TABLE testtable (col1 date, col2 datetime, col3 datetime2); |
PostgreSQL
CREATE TABLE testdate (col1 date, col2 timestamp(3), col3 timestamp); |
Conclusion
While migrating from Oracle to PostgreSQL, if a column with DATE data type includes also the time, then, we must carefully choose the target data type in PostgreSQL. A good equivalent in such a case may be timestamp(0). Similarly, when we are migrating from SQL Server to PostgreSQL, a DATE or DATETIME or DATETIME2(n) do have perfect alternatives in Postgres. It is important to choose the appropriate target data type in Postgres to avoid migration failures and problematic application behavior. In case you want to have a detailed clarity on the appropriate target data types to be used during the process of migration, please Contact Us, and we shall be happy to support you.
Fill the following form if you are in the process of migrating to PostgreSQL.
