0

I'm using oracle database with my java application. In DB i have created a table with column of type DATE and then my application populated this table with some dates with precision to seconds.

Now my application is able to retrieve date with precision to seconds and everything works fine. However I'm using Oracle SQL Developer to keep an eye on what is in the database, and here comes my problem, when I look inside my table from table view in column with DATE I can see only date in format of 16/09/07 without time of hours/minutes/seconds.

Also if I run a script:

select SAVED_DATE from MY_TABLE;

it returns all dates in format of 16/09/07 without time of hours/minutes/seconds.

How can I inspect from Oracle SQL Developer precise time stored in that column?

Improve this question
2
  • 2
    NLS format settings. Tools --> preferences--> Database-->NLS set your date/timestamp format to be format desired, or use to_Char to format date to show time portions. Select to_Char(sysdate,'DD/MM/YYYY HH24:MI:SS') from dual or see here for more info on NLS settings Commented Sep 7, 2016 at 12:44
  • @Jens Oracle has DATE and TIMESTAMP data types and both have a time component. Oracle does not have a separate DATETIME data type. Commented Sep 7, 2016 at 13:49

2 Answers 2

Reset to default
1

See ixora. On a contrary to other databases and also to Java, Oracle's DATE datatype also contains hours, minutes and seconds.

byte 1: century + 100

byte 2: year + 100

byte 3: month

byte 4: day of month

byte 5: hour + 1

byte 6: minute + 1

byte 7: second + 1

What confuses you is an implicit datatype conversion to VARCHAR2. This is driven by session parameter NLS_DATE_FORMAT.

xQbert is right, change NLS_DATE_FORMAT in SQLDeveloper settings to "YYYY-MM-DD HH24:MI:SS".

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

2 Comments

thank you, explained everything.
@AkkaJaworek Dates not stored in a table (type 13) are 8-bytes and dates stored in tables (type 12) are 7-bytes - see SO Documentation for the Format of a Date and the SO Documentation for Setting the Default Format Model of a Date.
0

A Date column in Oracle databases will store information with a precision down to seconds. The default returned data when selecting from this column will be DD-MON-YY.

It is possible to get the original precision by using to_char when selecting. For example:

SELECT to_char(SAVED_DATE,'YYYY-MM-DD HH24:MI:SS') FROM MY_TABLE

will return a date like 2016-09-07 14:58:30

Improve this answer

4 Comments

is it possible that date retrived from database by java application has date with proper time of hours/minutes/seconds? This is example of what Hibernate returns me when i ask for date from database 2016-09-07 11:24:55.0
Once the data has been stored in a Date field, there is no way to retrieve the original data with a precision smaller than days.
Not in oracle. Oracle stores the time portion on a date data type: See this example: create table foo ( mDate date, mtimestamp timestamp); insert into foo values (to_date('2016-1-1 15:23:33', 'YYYY-MM-DD HH24:MI:SS'),sysdate); Select A.*, to_char(mdate,'YYYY-MM-DD HH24:MI:SS') from foo A;
Ah look at that, Looks like I misread my source. I'll update the answer

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.