How to convert oracle timestamp to postgres?

Question

How to convert the oracle timestamp to postgres timestamp,we have a values in oracle data base as below,

15-JUN-2014 01.00.00.0000 PM

I have stored as varchar in postgres/Greenplum. How can I cast to timestamp in postgres,

Output required As below ,

15-06-2014 13.00.00.0000

As 24 hrs format.

Timestamps don't have "a format" - neither in Oracle nor in Postgres (or in any other DBMS). How are you transferring the data? How are you connecting the two databases? — user330315
– user330315, Commented Sep 29, 2014 at 17:49
from oracle we are generating csv files and loading into greenplum in that case i am not able to convert am/pm into 24 hrs format in postgress/greenplum. — William R
– William R, Commented Sep 29, 2014 at 18:00
The why don't you create the CSV file with the correct timestamp format right away? — user330315
– user330315, Commented Sep 29, 2014 at 18:51
total size of file is more than 100 gb , i dont want to re-create again by sitting more than 2 -3 days :) — William R
– William R, Commented Sep 30, 2014 at 4:40

Sylvain Leroux · Accepted Answer · 2014-09-30 04:32:35Z

3

Timestamps are timestamps. They represent a date (incl. time) using an internal representation. But you can convert timestamps to/from strings using various formats. For example, if you need to display them or in order to export your data to an other software requiring some specific representation.

In the following code for Oracle, I convert from a string to a timestamp (TO_TIMESTAMP) using a format corresponding to your first example. Then I "convert back" that timestamp to a string (TO_CHAR) using an other format corresponding to your second representation:

-- Convert a string to a timestamp
WITH sample_data AS 
  (SELECT TO_TIMESTAMP('15-JUN-2014 01.00.00.0000 PM', 
                       'DD-MON-YYYY HH12.MI.SS.FF4 PM') ts FROM DUAL)

-- Convert a timestamp to a string using a format corresponding
-- to "15-06-2014 13.00.00.0000"
SELECT TO_CHAR(ts, 'DD-MM-YYYY HH24.MI.SS.FF4') as result FROM sample_data;

Producing (the string):

result
15-06-2014 13.00.00.0000

Please note that you can convert from string to date in PostgreSQL too using (almost?) exactly the same date functions and formats.

edited Sep 30, 2014 at 4:32

answered Sep 29, 2014 at 17:49

Sylvain Leroux

52.3k8 gold badges114 silver badges136 bronze badges

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

2 Comments

William R Over a year ago

Still not working it's not converting to 24hrs format

select TO_TIMESTAMP(event_timestamp,'DD-MON-YY HH24.MI.SS.FF4')::TIMESTAMP WITHOUT TIME ZONE from jiodba.ext_WFA_ACCOUNTING_MSG limit 100 --"2014-07-01 12:01:18"

Theo F Over a year ago

this solution strips the 'PM' from the end of the timestamp. How can you maintain the Oracle formatting in PostgreSQL?

William R · Accepted Answer · 2014-09-30 09:42:50Z

0

SELECT To_timestamp(event_timestamp,'DD-MON-YY HH12.MI.SS.SSSS AM.PM')::timestamp without time zone
FROM jiodba.ext_wfa_accounting_msg limit 1000;

Please find the answer

answered Sep 30, 2014 at 9:42

William R

7393 gold badges14 silver badges38 bronze badges

Collectives™ on Stack Overflow

How to convert oracle timestamp to postgres?

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related