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', 'peter@hotmail.com', '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?

Have you tried using `DATE` instead of `TIMESTAMP`, as your column's name suggest it is only a date, not a timestamp? — Usagi Miyamoto, Mar 20 '17 at 12:24

score 1 · Answer 1 · edited May 23 '17 at 12:09

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)

edited May 23 '17 at 12:09

Community

1
1

answered Sep 25 '16 at 09:57

Alex

189
1
7

1

That answer does not cover how to store timestamps in UNIX timestamp format in H2 database – Smajl Sep 25 '16 at 12:11
1

you can't... you are trying to use the Timestamp sql type as a representation of a datatype somehow like a "long"... why not using directly a LONG type or NUMERIC(18) in that case ? SQL Timestamp is NOT what you think it is : even if in the end of the serialization on the disk the database surely use something like unix representation, the front end of the H2 SQL API says TIMESTAMP = Date+Time+Milliseconds in a text representation. so if you really want to use Timestamp SQL type to represent you Unix timestamp, you have to do the conversion by yourself, no cookie. – Alex Sep 25 '16 at 12:36
Ok, I decided I will store it int the standard text format and I am trying to save it like this: '2016-12-01 20:00'. However, this is causing exception: Cannot parse "TIMESTAMP" constant "2016-12-01 20:00". I am using DATETIME data type. – Smajl Sep 28 '16 at 08:29

How to store timestamp in h2 database

1 Answers1