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?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Akka Jaworek
  • 1,970
  • 4
  • 21
  • 47
  • 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](http://stackoverflow.com/questions/8134493/how-can-i-set-a-custom-date-time-format-in-oracle-sql-developer) for more info on NLS settings – xQbert Sep 07 '16 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. – MT0 Sep 07 '16 at 13:49

2 Answers2

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".

Community
  • 1
  • 1
ibre5041
  • 4,903
  • 1
  • 20
  • 35
  • thank you, explained everything. – Akka Jaworek Sep 07 '16 at 12:59
  • @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](http://stackoverflow.com/documentation/oracle/2087/dates/6848/the-format-of-a-date) and the [SO Documentation for Setting the Default Format Model of a Date](http://stackoverflow.com/documentation/oracle/2087/dates/6850/setting-the-default-date-format-model). – MT0 Sep 07 '16 at 13:53
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

Cas
  • 758
  • 14
  • 36
  • 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`` – Akka Jaworek Sep 07 '16 at 12:51
  • 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. – Cas Sep 07 '16 at 12:53
  • 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;` – xQbert Sep 07 '16 at 12:54
  • Ah look at that, Looks like I misread my source. I'll update the answer – Cas Sep 07 '16 at 12:55