1

I have quite a headache of an issue. I am moving a Timestamp with time zone field in Postgresql to a Date (datetime) column in Oracle, using an ETL tool.

The Postgresql Timestamp column (named psql_column) has values such as:

2011-09-13 07:37:24+00

While Oracle expects values in the form of:

DD.MM.YYYY HH24.MI.SS

I have tried several queries on the Postgresql table to retreive the values in a manner suitable to Oracle, such as:

to_char(psql_column, 'DD.MM.YYYY HH24.MI.SS')

Which oracle does not want to accept for some reason (throws ORA-01830)? Then the following line casts the value back to a date value:

to_date(to_char(psql_column, 'DD.MM.YYYY HH24.MI.SS'), 'DD.MM.YYYY HH24.MI.SS')

But this ofcourse only returns the value back to the original Postgresql date format, which Oracle doesn't accept.

bullfighter
  • 397
  • 1
  • 4
  • 21
  • Quit asking the same question [over](https://stackoverflow.com/questions/76590004/ora-01830-incorrect-date-with-timestamp-from-postgresql-to-oracle-format) and [over](https://stackoverflow.com/questions/76595596/ora-01830-date-with-time-insert). You might also want to read this [Oracle default date formats](https://stackoverflow.com/questions/50163432/oracles-default-date-format). – Adrian Klaver Jul 01 '23 at 18:39
  • This is another side to the problem - same overall issue, but honing into the Postgresql component of what I am working on, NOT the Oracle side of things. – bullfighter Jul 01 '23 at 18:43
  • Read your question in particular: *Which oracle does not want to accept for some reason (throws ORA-01830)?* and *But this ofcourse only returns the value back to the original Postgresql date format, which Oracle doesn't accept.* . The issue is on the Oracle side of things not in Postgres per: `select to_char('2023-07-01 11:45'::timestamp, 'DD.MM.YYYY HH24.MI.SS') 01.07.2023 11.45.00`. Postgres is delivering what you are asking for. – Adrian Klaver Jul 01 '23 at 18:48
  • The issue is ultimately due to schema reconciliation in the ETL tool. I can format the date correctly out from Postgres, yes, but it will be in a char-type, which tries to insert a value of the format 'DD.MM.YYYY HH24.MI.SS' as a string to Oracle, which will keep throwing the ORA-01830 error. So one way to fix this would be to cast to timestamp in postgres, but fix the format it returns it as - otherwise the format won't match and be accepted by Oracle – bullfighter Jul 01 '23 at 18:53
  • My apologies, I found the fix just now. Simply casting the column to a regular Timestamp in Postgresql removes the timezone component, which is what I assume Oracle was complaining about in the first place. – bullfighter Jul 01 '23 at 18:56
  • There is no time zone component in `'DD.MM.YYYY HH24.MI.SS'` or it's output `01.07.2023 11.45.00`. I'm betting that the actual issue was with this format `'DD.MM.YYYY HH24.MI.SS'` instead of using `2011-09-13 07:37:24` or `YYYY-MM-DD HH24.MI.SS` – Adrian Klaver Jul 01 '23 at 20:49

1 Answers1

0

try this format, I guess it should work:)

to_date(to_char(psql_column, 'DD.MM.YYYY HH24:MI:SS'), 'DD.MM.YYYY HH24:MI:SS')
Baktybek Baiserkeev
  • 577
  • 4
  • 4