How to store timestamp in h2 database

Question

I have a simple REST Api exposing several entities, some of which, has date field which I would like to store in a H2 database in the UNIX timestamp format. However, this is causing the followign exceptions:

Caused by: org.h2.jdbc.JdbcSQLException: Cannot parse "TIMESTAMP" constant "335923000"; SQL statement:
INSERT INTO people (person_id, first_name, last_name, email, gender, location, date_birth) VALUES 
    ('1', 'Peter', 'Smith', '[email protected]', 'MALE', 'London', 335923000),
    ...

Previously, it worked with timestamp in the following format: '1980-04-08'.

This is the SQL table definition:

CREATE TABLE people (
    person_id BIGINT PRIMARY KEY auto_increment,
    first_name VARCHAR(32),
    last_name VARCHAR(32),
    email VARCHAR(128) UNIQUE,
    gender VARCHAR(8),
    location VARCHAR(32),
    date_birth TIMESTAMP,
);

and my mapping object (unnecessary details omitted):

@Entity
@Table(name = "people")
@Getter
@Setter
public class Person {

    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    @Column(name = "person_id")
    private long id;

    ...

    @Column(name = "date_birth")
    @JsonFormat(pattern = "YYYY-MM-dd")
    private Date dateOfBirth;

    ...
}

I assume the the @JsonFormat annotation does not have anything to do with the database timestamp format but I am not sure how to tell hibernate that my timestamp are in UNIX format.

I have looked at convert unix timestamp to H2 timestamp and H2 docs and this standard format should be supported. Could someone point out to me, what am I doing wrong?

Answer 1

well, the answer have been done in another manner here

you'll find a way to represent a datetime with milliseconds without timezone (that is what is "timestamp" is in SQL)