how to insert a "TIMESTAMP WITH TIMEZONE" value?

3,728 views

Skip to first unread message

Kriss Lu

unread,

Sep 5, 2016, 6:15:51 AM9/5/16

to H2 Database

I am running H2 1.4.192 which declares support "timestamp with timezone" data type. I create a table as below:

create table TEST_TIME(ID INT PRIMARY KEY, NAME TIMESTAMP WITH TIMEZONE);

I try to insert a record like this:

insert into TEST_TIME values (1, CURRENT_TIMESTAMP())

It reports an error:

Data conversion error converting "2016-09-05 18:05:11.685+00"; SQL statement:

insert into TEST_TIME values (1, CURRENT_TIMESTAMP()) [22018-192] 22018/22018 (Help)

I also try to insert record like this:

insert into TEST_TIME values (2, PARSEDATETIME('Sat, 3 Feb 2001 03:05:06 GMT', 'EEE, d MMM yyyy HH:mm:ss z', 'en', 'GMT'))

It reports the same error.

Could somebody shed me a light on how to inserve a "timestamp with timezone" value? The document of H2 has no information on it. Thanks in advance.

Noel Grandin

unread,

Sep 5, 2016, 7:05:16 AM9/5/16

to h2-da...@googlegroups.com

Currently, all it supports is

(a) ISO 8601 time in string format.
https://en.wikipedia.org/wiki/ISO_8601#Time_zone_designators

(b) using preparedstatement and org.h2.api.TimestampWithTimeZone

Kriss Lu

unread,

Sep 5, 2016, 10:10:26 PM9/5/16

to H2 Database

Hi Noel,

Thanks for your quick response. I will have a try.

在 2016年9月5日星期一 UTC+8下午7:05:16，Noel Grandin写道：

Kriss Lu

unread,

Sep 6, 2016, 2:57:59 AM9/6/16

to H2 Database

Hi Noel,

Thanks for your quick response firstly. I try to insert a record using preparedstatement but fails.

My codes is:

JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
String insertSql = "insert into TEST_TIME values (?, ?)";
// current time with time zone UTC+8
final TimestampWithTimeZone ts = new TimestampWithTimeZone(new Date().getTime(), 0, (short) 8);
jdbcTemplate.update(insertSql, new PreparedStatementSetter() {
@Override
public void setValues(PreparedStatement ps) throws SQLException {
ps.setInt(1, 1);
ps.setObject(2, ts); //ps.setTimestamp(2, ts) //have same result as setObject()

}
});

The execution of the above code fails with an exception:

INFO: SQLErrorCodes loaded: [DB2, Derby, H2, HSQL, Informix, MS-SQL, MySQL, Oracle, PostgreSQL, Sybase, Hana]
[error] (run-main-6) org.springframework.jdbc.UncategorizedSQLException: PreparedStatementCallback; uncategorized SQLException for SQ
0000]; General error: "java.lang.ArrayIndexOutOfBoundsException: 2048"; SQL statement:
[error] insert into TEST_TIME values (?, ?) [50000-192]; nested exception is org.h2.jdbc.JdbcSQLException: General error: "java.lang.
[error] insert into TEST_TIME values (?, ?) [50000-192]
org.springframework.jdbc.UncategorizedSQLException: PreparedStatementCallback; uncategorized SQLException for SQL [insert into TEST_T
"java.lang.ArrayIndexOutOfBoundsException: 2048"; SQL statement:
insert into TEST_TIME values (?, ?) [50000-192]; nested exception is org.h2.jdbc.JdbcSQLException: General error: "java.lang.ArrayInd
insert into TEST_TIME values (?, ?) [50000-192]
at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.j
at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.j
at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.j
at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:649)
at org.springframework.jdbc.core.JdbcTemplate.update(JdbcTemplate.java:870)
at org.springframework.jdbc.core.JdbcTemplate.update(JdbcTemplate.java:931)
at org.kriss.MyJava.main(MyJava.java:31)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:606)
Caused by: org.h2.jdbc.JdbcSQLException: General error: "java.lang.ArrayIndexOutOfBoundsException: 2048"; SQL statement:
insert into TEST_TIME values (?, ?) [50000-192]
at org.h2.message.DbException.getJdbcSQLException(DbException.java:345)
at org.h2.message.DbException.get(DbException.java:168)
at org.h2.message.DbException.convert(DbException.java:295)
at org.h2.table.RegularTable.addRow(RegularTable.java:137)
at org.h2.command.dml.Insert.insertRows(Insert.java:156)
at org.h2.command.dml.Insert.update(Insert.java:114)
at org.h2.command.CommandContainer.update(CommandContainer.java:98)
at org.h2.command.Command.executeUpdate(Command.java:258)
at org.h2.jdbc.JdbcPreparedStatement.executeUpdateInternal(JdbcPreparedStatement.java:160)
at org.h2.jdbc.JdbcPreparedStatement.executeUpdate(JdbcPreparedStatement.java:146)
at org.springframework.jdbc.core.JdbcTemplate$2.doInPreparedStatement(JdbcTemplate.java:877)
at org.springframework.jdbc.core.JdbcTemplate$2.doInPreparedStatement(JdbcTemplate.java:870)
at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:633)
at org.springframework.jdbc.core.JdbcTemplate.update(JdbcTemplate.java:870)
at org.springframework.jdbc.core.JdbcTemplate.update(JdbcTemplate.java:931)
at org.kriss.MyJava.main(MyJava.java:31)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:606)
Caused by: java.lang.ArrayIndexOutOfBoundsException: 2048
at org.h2.store.Data.writeByte(Data.java:386)
at org.h2.store.Data.writeValue(Data.java:558)
at org.h2.index.PageDataLeaf.addRowTry(PageDataLeaf.java:217)
at org.h2.index.PageDataIndex.addTry(PageDataIndex.java:172)
at org.h2.index.PageDataIndex.add(PageDataIndex.java:135)
at org.h2.table.RegularTable.addRow(RegularTable.java:119)
at org.h2.command.dml.Insert.insertRows(Insert.java:156)
at org.h2.command.dml.Insert.update(Insert.java:114)
at org.h2.command.CommandContainer.update(CommandContainer.java:98)
at org.h2.command.Command.executeUpdate(Command.java:258)
at org.h2.jdbc.JdbcPreparedStatement.executeUpdateInternal(JdbcPreparedStatement.java:160)
at org.h2.jdbc.JdbcPreparedStatement.executeUpdate(JdbcPreparedStatement.java:146)

But it does insert a record in the table TEST_TIME although the time zone(UTC+8) info is missing :

SELECT * FROM TEST_TIME;
ID NAME
1 2016-09-06 14:41:05.0+00
(1 row, 8 ms)

ID	NAME
1	2016-09-06 14:41:05.0+00

Could you please have a look at where the issue is? It is greatly appreciated that you can provide a workable sample about inserting "timestamp with timezone" record. Thanks!