Problem with Date field after migration from mysql to oracle

I have lately migrate my JIRA database from mysql to oracle, my problem is the field "created" exists in the jiraissue and changegroup tables,on this field I effectuate many calculation but I was surprised by the difference of the format of the fields.

in mysql database the field creation has the type timeStamp so it has the follwing format:

and in Oracle database it has the type date 开发者_运维技巧and the format like the following:

How can I resolve this problem?

The format of your displayed Oracle DATE column is due to your IDE that you are viewing it through.

Oracle stores all portions of a date, to display the full date stored use this:

SELECT TO_CHAR(created, 'DD-MON-YYYY HH24:MI:SS')
  FROM jiraissue;

This will show you the full date that has been stored including the time portion. To store timestamps you need the column to be designated as a timestamp datatype column.

Ollie.

EDIT: You could change the NLS Date Format of your IDE to always show the full date format in it's settings somewhere.

Dates are not held as formatted text in Oracle. What you are seeing is a tool's (Toad's?) default formatting of the date to display it to you. This can be changed via a preference somewhere. It could be that the time component has been lost in migration, but that is unlikely. Try running this SQL to see:

select to_char (created, 'YYYY-MM-DD HH24:MI:SS') from jiraissue;

That should show the dates just as they appeared in MySQL.