2

This is my column of entity, which called createdDate, which I found by this value of User's phone-number and message text. In MySQL it worked without problem.

@Column(name = "created_date", insertable = false, updatable = false,
        columnDefinition = "TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP")
private LocalDateTime created_date;

But generally, I work with Oracle and I want to declare createdDate value as CURRENT_TIMESTAMP like MySQL. I've added it like this, it worked.

@CreationTimestamp
@ColumnDefault("CURRENT_TIMESTAMP")
protected LocalDateTime createdDate;

But value of createdDate stored to table like this 2021-10-15 14:46:27.219000 I don't want to like this, I want to stored the data to database like this format

yyyy-MM-dd HH:mm:ss

I somehow didn't give the default value using Oracle database in JPA. How to solved? is there any way, which solved or remove end of second value?

Mark Rotteveel
  • 100,966
  • 191
  • 140
  • 197
zordu-nickim-agzivi-sikm
  • 193
  • 1
  • 2
  • 14
  • 3
    `TIMESTAMP` columns don't have any format at all. Any format you see, is applied by the SQL client you are using when _displaying_ the value. –  Oct 15 '21 at 11:14
  • 1
    Have you tried `CURRENT_TIMESTAMP(0)`? – Mark Rotteveel Oct 15 '21 at 12:34
  • What @a_horse_with_no_name has mentioned is very important and should clear your doubt. If you still have some doubts, I suggest you check [The standard library does not support a formatted Date-Time object.](https://stackoverflow.com/a/68009408/10819573) which is related to Date-Time types in Java but the fact remains the same i.e. a DB or a language stores/processes the information of a Date-Time type, not the formatting. – Arvind Kumar Avinash Oct 16 '21 at 12:38

1 Answers1

0

Oracle stores timestamp as packets of bits, each one representing specific information. Any client program then can show the value in the format they prefer. In your case, if you want to show them as YYYY-MM-DD

alter session set nls_timestamp_format='YYYY-MM-DD';

If you do it in JPA, and you are using a pool, it would affect all the connections using such pool.

Example

SQL> create table t ( c1 timestamp ) ;

Table created.

SQL> insert into t values ( systimestamp ) ;

1 row created.

SQL> select * from t ;

C1
---------------------------------------------------------------------------
15-OCT-21 01.29.45.427944 PM

SQL> select dump(c1) from t ;

DUMP(C1)
--------------------------------------------------------------------------------
Typ=180 Len=11: 120,121,10,15,14,30,46,25,129,232,64

In my case, the value is shown using my default timestamp format

SQL> select parameter,value from v$nls_parameters where parameter='NLS_TIMESTAMP_FORMAT' ;

PARAMETER                     VALUE                                                                     
---------------------------------------------------------------- ----------
NLS_TIMESTAMP_FORMAT          DD-MON-RR HH.MI.SSXFF AM

Let's dig in what those values of the dump function means. For our timestamp 15-OCT-21 01.29.45.427944 PM we got the following dump :

120,121,10,15,14,30,46,25,129,232,64

  • Century: (120-100)*100 = 2000
  • Year: (121-100) = 21 ( as century is 2000 + 21 = 2021 )
  • Month: 10
  • Day: 15
  • Hour: 14 ( excess - 1 ) then 13
  • Minute: 30 ( excess - 1 ) then 29
  • Second: 46 ( excess - 1) then 45

The remaining part is the specific fraction of seconds stored in the timestamp value ( remember that timestamp in Oracle stores fraction of seconds up to nanoseconds ). As the dump is type 180, there is not time zone associated. Below you can find a link to an amazing answer from @Alex Poole explaining more in detail what I just told you here, plus the other types of timestamp with time zone.

Timestamp Bytes

Roberto Hernandez
  • 8,231
  • 3
  • 14
  • 43