Invalid DateTimeFormat when inserting date in H2 in memory DB-CodePudding

I'm having a problem trying to insert values in a in-memory H2 DB.

I have this Entity:

@Entity
@Table(name="myTable")
public class MyEntity {

    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    private long id;
    
    @Column(name = "startDate")
    @DateTimeFormat(pattern="yyy-MM-dd-HH.mm.ss")
    private Date startDate;
    
    @Column(name = "endDate")
    @DateTimeFormat(pattern="yyy-MM-dd-HH.mm.ss")
    private Date endDate;
}

I've used the pattern yyy-MM-dd-HH.mm.ss because the dates are in format like 2021-06-14-00.00.00 or 2021-12-31-23.59.59.

The @DateTimeFormat imported is org.springframework.format.annotation.DateTimeFormat; and Date is java.util.Date.

To insert values in the DB I've this script:

INSERT INTO myTable (ID, START_DATE, END_DATE) VALUES
(1, '2020-08-07-00.00.00', '2021-12-31-23.59.59'),
(2, '2020-04-03-14.00.00', '2021-01-14-18.30.00'),
(3, '2020-09-15-00.00.00', '2021-06-15-11.00.00'),
(4, '2020-01-18-16.00.00', '2021-12-31-23.59.59');

But it throw an error:

Caused by: org.h2.jdbc.JdbcSQLDataException: Imposible interpretar la constante "TIMESTAMP" "2021-12-31-23.59.59"
Cannot parse "TIMESTAMP" constant "2021-12-31-23.59.59"; SQL statement:
INSERT INTO myTable (ID, ...

...

Caused by: java.lang.NumberFormatException: 31-23.59.59

The text in the error which is not in english is something like "Unable to interpret constant "TIMESTAMP"".

Also I have tested a few things and, (this is important!) removing hours, minutes and seconds, the program works, so I assume is the pattern which is not correct.

This SQL script works:

INSERT INTO myTable (ID, START_DATE, END_DATE) VALUES
(1, '2020-08-07', '2021-12-31'),
(2, '2020-04-03', '2021-01-14'),
(3, '2020-09-15', '2021-06-15'),
(4, '2020-01-18', '2021-12-31');

I've checked a DateTimeFormat tester online and the current date with my pattern is 2021-12-21-17.44.32, but even creating the table using only that date it thrown the error.

More info about the table:
The table (and also the columns) is created using spring.jpa.hibernate.ddl-auto=update so I assume the column type is DATE (as the entity field).

So I'm trying to insert a String with format yyy-MM-dd-HH.mm.ss in a DATE column (if I'm not wrong).

So, what's the problem? Is not possible to insert this pattern as date from string in an H2 in memory DB?

Thanks in advance.

CodePudding user response：

Your problem is not the JPA layer defined in your Java code but the scripts that you execute.

INSERT INTO myTable (ID, START_DATE, END_DATE) VALUES
(1, '2020-08-07-00.00.00', '2021-12-31-23.59.59'),
(2, '2020-04-03-14.00.00', '2021-01-14-18.30.00'),
(3, '2020-09-15-00.00.00', '2021-06-15-11.00.00'),
(4, '2020-01-18-16.00.00', '2021-12-31-23.59.59');

This has nothing to do with how you have defined your JPA layer. For that script when executed the @DateTimeFormat(pattern="yyy-MM-dd-HH.mm.ss") is completely unknown that you have defined in your code.

This is just a script executed in DB. How would the DB know how to convert this String into a valid Date object? You must inform in your script how the conversion from String to Date must happen.

The following should work as the PARSEDATETIME is the function for H2 for that reason.

INSERT INTO myTable (ID, START_DATE, END_DATE) VALUES (1, PARSEDATETIME('2020-08-07-00.00.00','yyyy-MM-dd-HH.mm.ss'), PARSEDATETIME('2021-12-31-23.59.59','yyyy-MM-dd-HH.mm.ss')

Please also check closely what you execute yyy-MM-dd-HH.mm.ss. yyy does not conform with the ages that you place in the scripts ex 2020. This is yyyy

CodePudding user response：

Little late but still:)

Assuming (h2, in-mem):

CREATE TABLE myTable(Id NUMBER primary key,START_DATE TIMESTAMP,END_DATE 
TIMESTAMP);

Then:

INSERT INTO myTable (ID, START_DATE, END_DATE) VALUES

like:

(1, {ts '2020-08-07 00:00:00'}, {ts '2021-12-31 23:59:59'}),

(with standard time format)

Or:

(2, parsedatetime('2020-04-03-14.00.00', 'yyyy-MM-dd-HH.mm.ss'), 
    parsedatetime('2021-01-14-18.30.00', 'yyyy-MM-dd-HH.mm.ss'));

(a custom one)

Refs:

CodePudding user response：

If you are just trying to insert a java.util.Date into a TIMESTAMP column you don't need @DateTimeFormat. You are not parsing anything, you part from the Date object, and JPA translates it into the proper SQL datatype.

Define the column this way:

    @Column(name = "startDate")
    @Temporal(value=TemporalType.DATE)
    private Date startDate;

And you should be good to go.

Update

If only DATE pattern works, and no TIMESTAMP patterns work, it means that the column has been defined with the wrong datatype. DATE and TIMESTAMP are two different H2 datatypes (and in all the other DDBBs but may be with different names)