0

I have migrated Oracle DB to PostGre DB. Upon migration, Date datatype in Oracle is changed to Timestamp datatype in Postgre.

For example, date value in Oracle- 27-May-22 is changed to 2022-02-27 09:44:07.000 in Postgre.

While I try to read the record from the table using Java and display to Jsp I am getting '**Invalid JSON response. For more information about this error, please see http://datatables.net/tn/1 **' alert message.

Now when I update the timestamp value to 2022-02-27 09:44:07.001 (notice the change in millisecond value from 000 to any value between 001 and 999) it works fine and no alert pops.

Any reason why this problem may occur and solution for that other than updating the values in the column?

Dan
  • 3,647
  • 5
  • 20
  • 26
vedant
  • 1
  • 1
  • 2
    Was the migration done manually? Why was the Oracle DB type `Date` migrated to a Postgres type `Timestamp`? That appears wrong because Postgres has a type `Date` which obviously doesn't have to deal with fractions of seconds. See [Date/Time types in Postgres](https://www.postgresql.org/docs/current/datatype-datetime.html) – deHaar May 27 '22 at 07:44
  • Better use `to_char(the_field, 'YYYY-MM-DD"T"HH24:MI:SS.MS')` to extract the timestamp in ISO8601 format. A good discussion [here](https://stackoverflow.com/questions/10286204/what-is-the-right-json-date-format) – Stefanov.sm May 27 '22 at 08:12
  • 2
    In Oracle, a `DATE` data type is a binary data type that consists of 7 bytes (century, year-of-century, month, day, hour, minute and second) and it **ALWAYS** has all of those components. Some client applications will default to only **display** the date component of the Oracle `DATE` data type but that does not mean that the time component does not exist; it just is not displayed. So the migration from an Oracle `DATE` to a PostgreSQL `TIMESTAMP` is to be expected as it is the natural data type to hold the same information. – MT0 May 27 '22 at 08:12
  • @deHaar the comment from MT0 explains my reason to interchange Date to Timestamp, moreover I cannot just use Date in Postgre as I also require 'time' to store and display – vedant May 27 '22 at 09:08
  • This seems to be a JAVA/Javascript issue rather then a database one, as there is nothing wrong with `2022-02-27 09:44:07.000` as a timestamp in Postgres: `select '2022-02-27 09:44:07.000'::timestamp; 2022-02-27 09:44:07`. What data type is the Javascript expecting? – Adrian Klaver May 27 '22 at 15:29
  • More to the point and should have been included in my previous comment: `select '2022-02-27 09:44:07.000'::date 2022-02-27`. – Adrian Klaver May 27 '22 at 16:09
  • Javascript is expecting text datatype – vedant May 30 '22 at 03:55

0 Answers0