Hi Ben,
2015-12-22 17:23 GMT+01:00 Ben Hood <[email protected]>:
> My initial implementation of this involved the following custom binding:
>
> @Override
> public void set(BindingSetStatementContext<ZonedDateTime> ctx) throws
> SQLException {
> ctx.statement().setTimestamp(ctx.index(), converter.to(ctx.value()),
> UTC);
> }
>
> whereby
>
> public static final Calendar UTC =
> Calendar.getInstance(TimeZone.getTimeZone("UTC"));
>
> This appears to insert timestamps into TIMESTAMP(6) columns in UTC
> (i.e. the default timestamp type in Oracle).
>
I've done some experiments recently and blogged about the results:
http://blog.jooq.org/2015/06/30/whats-even-harder-than-dates-and-timezones-dates-and-timezones-in-sql-jdbc
Ultimately, it is important to know that TIMESTAMP(6) in Oracle is a
TIMESTAMP WITHOUT TIME ZONE. I.e. a LocalDateTime. It will produce
different UTC timestamps in case your database ever doesn't run in UTC.
Also, the client time zone does affect the real timestamp that is fetched
here.
I'm assuming that the storage of all timestamps in the DB is going to
> be in UTC and therefore I don't need to use the TIMESTAMP WITH TIME
> ZONE type. Any translation into a non-UTC TZ would have to either be
> coded into a query or converted by the app.
>
> The main motivation behind this that I've read (on the internet, hence
> it must be true) that there are indexing limitations with the
> TIMESTAMP WITH TIME ZONE type.
>
Interesting, I wasn't aware of this. Do you have a link?
--
You received this message because you are subscribed to the Google Groups "jOOQ
User Group" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
For more options, visit https://groups.google.com/d/optout.
