CODEWITHSUNDEEP
javaoracle-databasejpajakarta-ee
Sergi Yagüe
Sergi Yagüe

Reputation: 55

Store Date and Time in Oracle Date Column

I am migrating a DB2 table to an Oracle database using JPA and JEE. Because of this, I'm having problems with the "Date" and "Time" type columns. More specifically, in Oracle there is no data type "Time", so I'm trying to store from DB2 the concatenation of 2 columns (one of type Date and another of type Time) inside a column of type "Date" of the Oracle database. This way, I show you how I defined the column in the Oracle database and the mapping I defined:

, DATA_HORA_CREACIO DATE DEFAULT
TO_DATE('0001-01-01-00.00.00','YYYY-MM-DD-HH24.MI.SS')
NOT NULL

private Date dataHoraCreacio;

@Temporal(TemporalType.TIMESTAMP)
@Column(name = "DATA_HORA_CREACIO", nullable = false)
public Date getDataHoraCreacio() {
    return this.dataHoraCreacio;
}

public void setDataHoraCreacio(Date dataHoraCreacio) {
    this.dataHoraCreacio = dataHoraCreacio;
}

SimpleDateFormat sdfDateHour = new SimpleDateFormat("yyyy-MM-dd-HH.mm.ss");
String data = sdfDate.format(comandaAvariaDb2.getId().getDataCreacio());
String hora = sdfHour.format(comandaAvariaDb2.getId().getHoraCreacio());
comandaAvariaOraId.setDataHoraCreacio(sdfDateHour.parse(data + "-" +    hora));

Even so, although the work variable that covered the information is correctly informed, the part referring to the time is stored and not inserted in the database:

Variable working data: Thu Feb 02 18:42:53 CET 2006
DataBase inserted Value: 2006-02-02

I've been trying things out for several hours without making it work. Even so, if I have managed to work with a column of type "Timestamp" but I do not think the best solution because the milliseconds occupy an unnecessary space.

Any ideas?

Thanks in advance.

Upvotes: 2

Views: 7364

Answers (1)

Jan
Jan

Reputation: 13858

If you use an Oracle DATE value for DATA_HORA_CREACIO, you should be able to directly insert timestamps from DB2. It's just that selecting a DATE column without any hints for output will give you only the date-part in display:

CREATE TABLE STACK2 
      (DATA_HORA_CREACIO DATE
    DEFAULT TO_DATE('0001-01-01-00.00.00',
        'YYYY-MM-DD-HH24.MI.SS') NOT NULL);

INSERT INTO STACK2 VALUES 
     (TO_DATE('2017-05-30-16.47.32','YYYY-MM-DD-HH24.MI.SS'));

SELECT DATA_HORA_CREACIO, 
       TO_CHAR(DATA_HORA_CREACIO, 'YYYY-MM-DD'), 
       TO_CHAR(DATA_HORA_CREACIO, 'HH24:MI:SS') FROM STACK2;

Result is: enter image description here

See this extract from Oracle DATE definition:

DATE Datatype

The DATE datatype stores date and time information. Although date and time information can be represented in both character and number datatypes, the DATE datatype has special associated properties. For each DATE value, Oracle stores the following information: century, year, month, date, hour, minute, and second.

Upvotes: 1

Related Questions