H2 database string to timestamp

Question

Insertion of timestamps in H2 database

Hello, I have to insert data like '17-09-2012 18:47:52.69'. Function PARSEDATETIME cuts milliseconds. Query example:

CREATE TABLE TEST(ID NUMBER(19) not null,
DATE TIMESTAMP DEFAULT CURRENT_TIMESTAMP);

INSERT INTO TEST (ID, DATE) VALUES(1,
parsedatetime('17-09-2012 18:47:52.69', 'dd-MM-yyyy hh:mm:ss.SS'))

After SELECT I see that milliseconds are zeroes.

What is solution?

Answer 1

According to my test, with H2 version 1.3.170, the milliseconds are not actually zero, but 069:

select * from test;
ID  DATE  
1   2012-09-17 18:47:52.069

The same happens if you run:

call parsedatetime('17-09-2012 18:47:52.69', 'dd-MM-yyyy hh:mm:ss.SS');

If you add a zero then it works:

call parsedatetime('17-09-2012 18:47:52.690', 'dd-MM-yyyy hh:mm:ss.SS');

H2 internally uses java.text.SimpleDateFormat, so it has to live with the same limitations. If you find a solution within SimpleDateFormat, you can use it within the parsedatetime function in H2.

An alternative is to use the ISO timestamp format as defined in JDBC. This is supposed to work with all databases that conform the JDBC standard:

INSERT INTO TEST VALUES(2, {ts '2012-09-17 18:47:52.69'});