why Oracle DATE type storing with time stamp and how to store only date-CodePudding

my oracle table define with Date type for one of the date column requestedDate.

while persist am setting the value like below

.setRequestedDate(Date.valueOf(LocalDate.now())))

And JPA entity define like below,

    @Column(name = "REQ_DT")
    @Temporal(TemporalType.DATE)
    private Date requestedDate;

I am expecting value like "2022-05-12" but it get stored like "2022-05-12 00:00:00.0" ? Just want to get ride of the timestamp when its gets insert intodb kindly suggest.

Do i need to change the date definition in oracle DB like creating View to truncate the timstamp.

CodePudding user response：

The Oracle DATE data type always includes a time component. If you don't specify one, then midnight is assumed. If you don't want the time portion to be displayed, then use the to_char function or some other native function in your programming language of choice in your application to control the display when the date is retrieved at runtime:

select to_char(sysdate,'YYYY-MM-DD') from dual;

TO_CHAR(SY
----------
2022-11-24

CodePudding user response：

If you want to insert value without time component, then truncate it. You can't get rid of it because DATE datatype - in Oracle - always contains both date and time

Have a look at the following example:

SQL> create table test (id number, datum date);

Table created.

Altering the session, just to display full format while selecting DATE datatype values:

SQL> alter session set nls_date_format = 'dd.mm.yyyy hh24:mi:ss';

Session altered.

SYSDATE is a function that returns current date and time; as you can see, DATE_ONLY contains date component, while time is set to midnight (but it is still here):

SQL> select sysdate           value_with_date_and_time,
  2         trunc(sysdate)    date_only
  3  from dual;

VALUE_WITH_DATE_AND DATE_ONLY
------------------- -------------------
25.11.2022 07:11:21 25.11.2022 00:00:00

If you select such values into a table:

SQL> insert into test (id, datum)
  2    select 1, sysdate            from dual union all
  3    select 2, trunc(sysdate)     from dual;

2 rows created.

Select from it:

SQL> select * from test order by id;

        ID DATUM
---------- -------------------
         1 25.11.2022 07:11:48       --> both date and time
         2 25.11.2022 00:00:00       --> also date AND time, but time is set to midnight

If you want to display values without time, either alter session and set another format model, or use to_char function; once again: this will just display date only, but value - stored in the table - will still have both date and time:

SQL> select id, to_char(datum, 'dd.mm.yyyy') display_date
  2  from test order by id;

        ID DISPLAY_DA
---------- ----------
         1 25.11.2022
         2 25.11.2022

SQL>

CodePudding user response：

why Oracle DATE type storing with time stamp

Because it was designed that way. The DATE is actually stored in the form of a number of (binary) numeric fields. See the answers to How are dates stored in Oracle? for the details of how DATE values are stored by Oracle databases.

... and how to store only date

If you want to use the Oracle DATE, you can't.

You could (of course) encode the date as a string or an integer and store that, and you would be free to exclude the time components. But then you would lose the benefits of using DATE operations in your queries.

But I think you are really missing the point here. It is irrelevant how the DATE values are stored in the database. What actually matters is how they are formatted:

Oracle allows you to control how dates are formatted by the SQL engine.
Your application is free to format the java.sql.Date (or whatever) it gets back from the database however it wants to.