Conversion of oracle timestamp to postgres timestamp?

Question

oracle timestamp example: 26-APR-17 09.40.13.243356000

Hi i have a scenario I am converting oracle timestamp to postgres timestamp but the output is not expected .

Query run in psql

migration=# SELECT TO_TIMESTAMP('26-APR-17 09.40.13.243356000','DD-MON-YY HH24.MI.SS:MS') :: timestamp without time zone;
        to_timestamp
----------------------------
   2017-04-26 09:15:55.864128                  ----- this is output 
(1 row)

The output should be: 2017-04-26 09:40:13.243356

No database is going to arbitrarily add a half hour. This sounds like a timezone issue, if one of the two databases uses the Indian timezone. You didn't post the actual code though. The values you posted look like the localized result of some query, not actual values. Very few countries use such delimiters. Only India uses a half-hour offset (that I know of) — Panagiotis Kanavos
– Panagiotis Kanavos, Commented Jun 8, 2017 at 10:09
To avoid localization issues use the ISO8601 format, ie YYYY-MM-DDTHH:MM:SS.ffffZ or YYYY-MM-DDTHH:mm:ss.ffff+HH:mm — Panagiotis Kanavos
– Panagiotis Kanavos, Commented Jun 8, 2017 at 10:10
@PanagiotisKanavos look at the picture . I have edited the question. — Himanshu sharma
– Himanshu sharma, Commented Jun 8, 2017 at 10:26

Laurenz Albe · Accepted Answer · 2017-06-08 10:36:48Z

3

The problem is that 243356000 is taken as milliseconds, which accounts for the strange offset of about half an hour.

Trim the last three sigits and use US for microseconds:

SELECT to_timestamp(
          regexp_replace(
             '26-APR-17 09.40.13.243356000',
             '\d{3}$',
             ''
          ),
          'DD-MON-YY HH24.MI.SS.US'
       );

answered Jun 8, 2017 at 10:36

Laurenz Albe

257k22 gold badges314 silver badges390 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

user330315 · Accepted Answer · 2017-06-08 10:26:34Z

2

The error is a result of the invalid value for the milliseconds format mask MS.

As documented in the manual the allowed values for milliseconds are from 000 to 999

.243356000 is however also not valid for microseconds (US format mask) because of the trailing zeroes.

If you want to use that format, you have to remove the zeros from the input string, e.g: using rtrim()

psql (9.6.3)
Type "help" for help.

postgres> SELECT TO_TIMESTAMP(rtrim('26-APR-17 09.40.13.243356000','0'),'DD-MON-YY HH24.MI.SS.US');
         to_timestamp
-------------------------------
 2017-04-26 09:40:13.243356+02
(1 row)

postgres>

I also agree with PanagiotisKanavos that it would be better to use a locale independent format (i.e. numbers for the month and a 4 digit year), preferably an ISO standard for timestamp values.

answered Jun 8, 2017 at 10:26

user330315

1 Comment

Laurenz Albe Over a year ago

That will lead to wrong results if the last three digits are not all 0.

Collectives™ on Stack Overflow

Conversion of oracle timestamp to postgres timestamp?

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related