How does H2 DB support TIMESTAMP WITH TIMEZONE DATATYPE

[scarlet...@gmail.com]

We are trying to move our product from Oracle to H2 database, but there is a problem that in our product using TIMESTAMP WITH TIMEZONE data type.

 

How does H2 database can support this kind of data type? Is there any suggestion?

 

PS. I do found this before add this topic:

https://groups.google.com/forum/#!searchin/h2-database/TIMESTAMP$20with$20timezone|sort:relevance/h2-database/1obbL2vYHAg/kC02qYqa304J

 

But the date of this topic is 2009 I guess if there is any change on h2 database to support this data type.

 

Thanks a lot.

[Noel Grandin]

We don't support this datatype at the moment.
You'd probably need to either implement it yourself as a custom datatype, using OTHER, or split it manually into a TIMESTAMP field and another field to represent the timezone.

Personally, I think you're better off simply storing your data using a normal TIMESTAMP date which stores these things as UTC internally.

Especially given that
(a) this datatype is not widely supported and
(b) the different databases (.e.g Oracle / PostgreSQL / MSSQL ) appear to implement this datatype differently.

[Jake Page]

Might be kind of an old thread, but to say "this datatype is not widely supported" is not true - it's ANSI SQL-92, and supported by Oracle, Postgres, and MSSQL (as you said), which makes it pretty widely supported by definition!

Since H2 in theory supports a lot of compatibility modes, this would probably be one of the biggest of those that's not currently supported...

[Noel Grandin]

Well, this is an open-source project, so patches are always welcome :-)

But that being said, I might have some time to work on this in the next few weeks, provided that you can provide me with
some input:

Do you know
(a) what the most common string representation of such a datatype is
(b) how it is returned in a ResultSet, since there is no JDBC datatype for it
(c) how it is passed into a PreparedStatement

[ram...@gmail.com]

+1

Would make me happy!