Timestamp in postgresql during oracle to postgresql migration

Question

I have a table in Oracle with timestamp data in "JAN-16-15 05.10.14.034000000 PM". When I created the table in postgresql with "col_name timestamp" it is showing data as "2015-01-16 17:10:14.034".

Any suggestions on how can set the column to get the data format as in postgre same to what I have in Oracle?

This is my timstamp in the database. Do I have to set something on database level in postgresql ----------db=> select current_timestamp; current_timestamp ------------------------------- 2019-03-22 13:40:55.482237+00 — Team
– Team, Commented Mar 22, 2019 at 13:41

Laurenz Albe · Accepted Answer · 2019-03-22 13:46:06Z

1

Timestamps (or dates or numbers) do not have any "format2. Neither in Postgres nor in Oracle or in any other relational database).

Any "format" you see, is applied by your SQL client displaying those values.

You need to configure your SQL client to use a different format for timestamp, or use the to_char() function to format the value as you want.

In particular, to get the format you desire, use

SELECT to_char(current_timestamp, 'MON-MM-YY HH.MI.SS.US000 AM');

The output format can be changed in psql by changing the DateStyle parameter, but I would strongly recommend to not change it away from the default ISO format as that also affects the input that is parsed.

edited Mar 22, 2019 at 13:46

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

answered Mar 22, 2019 at 13:42

user330315

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

Collectives™ on Stack Overflow

Timestamp in postgresql during oracle to postgresql migration

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related