Hi Lukas,
Sorry for the radio silence - I've been busy chasing down timezone
issues with the reports that are built off the back of this app.
Thanks for the alternative routes that you've suggested.
Unfortunately my app does a lot of date time arithmetic in the data
base, i.e. making heavy use of TRUNC(DATE), EXTRACT(MONTH AT TIME ZONE
x), MULTISET CONNECT BY ADD_MONTHS(), LAST_DAY() as well as storing
in UTC but rolling up in user defined timezones.
But I've managed to get JOOQ to fire off OffsetDateTime instances down
to the DB thusly:
final static DateTimeFormatter DATE_TIME_FORMATTER =
DateTimeFormatter.ofPattern("yyyy-MM-dd'T'HH:mm:ss.SSSZZZ");
static Field<OffsetDateTime> format(OffsetDateTime odt) {
Object value = (odt == null) ? val(null) : odt.format(DATE_TIME_FORMATTER);
return field("{to_timestamp_tz}({0}, {1})", OffsetDateTime.class,
value, "YYYY-MM-DD\"T\"HH24:MI:SS.ff3 TZH:TZM");
}
Maybe there is a more elegant way to do this, but it seems to work and
I've got rid of masses of timezone bugs.
That said, I've now just started to programmatically read from the DB
and I've run into a read issue with JOOQ (whereas the previous part of
this thread was dealing with the write path) and I'm getting the
following issue:
java.time.format.DateTimeParseException: Text '2014-04-01T10:11:10.15
+0:00' could not be parsed at index 22
at
java.time.format.DateTimeFormatter.parseResolved0(DateTimeFormatter.java:1949)
at java.time.format.DateTimeFormatter.parse(DateTimeFormatter.java:1851)
at java.time.OffsetDateTime.parse(OffsetDateTime.java:402)
at java.time.OffsetDateTime.parse(OffsetDateTime.java:387)
at org.jooq.impl.DefaultBinding.offsetDateTime(DefaultBinding.java:1496)
at org.jooq.impl.DefaultBinding.get(DefaultBinding.java:1336)
at
org.jooq.impl.CursorImpl$CursorIterator$CursorRecordInitialiser.setValue(CursorImpl.java:1640)
The offending code that causes this is:
Cursor<Record1<OffsetDateTime>> cursor =
ctx.select(CDR_IDENTIFIERS.EFFECTIVE).from(CDR_IDENTIFIERS).fetchLazy();
while (cursor.hasNext()) {
// BOOM!
}
Is a way to register a Converter instance via the DSL API in order to
override the default Converter? Or is this only possible with code
gen?
Cheers,
Ben
On Mon, Jan 4, 2016 at 4:32 PM, Lukas Eder <[email protected]> wrote:
> Hi Ben,
>
> I'm sorry for the delay in this matter.
>
> It is true that binding the Oracle TIMESTAMP WITH TIME ZONE data type brings
> a couple of caveats. Unfortunately, I cannot tell you from experience what
> will happen, perfomance-wise, if you use one solution rather than the other.
> I can, however, confirm that what you feel is a bit hacky might just be as
> good as it gets right now. I've reviewed other customers' Binding
> implementations that attempt to bind the java.sql.SQLXML type, which needs
> to be free'd explicitly via SQLXML.free() after statement execution (similar
> to Blob, Clob). Things start getting just as hairy.
>
> Usually, with these exotic types, people are happy if they can just
> serialize / deserialize them to / from the server (e.g. XML). In your case,
> I agree that there is significant concern regarding runtime performance, but
> unfortunately, I'm as wise as you are right now.
>
> One approach that I've seen in the past is people storing just
> java.lang.Long values for what corresponds to java.time.Instant. As long as
> you don't need any date time arithmetic in the database, this might be
> enough, and is certainly not a performance issue.
>
> Another option might be to implement a Binding that completely ignores the
> serialisation of the TIMESTAMP WITH TIME ZONE type, binds (and reads)
> java.lang.Long, and converts the number into a timestamp by generating some
> additional SQL, e.g. as explained here:
> http://stackoverflow.com/a/15520654/521799
>
> Hope this helps.
> Let me know if, in the meantime, you have any additional questions.
>
> Best Regards,
> Lukas
>
> 2015-12-23 3:32 GMT+01:00 Ben Hood <[email protected]>:
>>
>> On Tue, Dec 22, 2015 at 9:08 PM, Ben Hood <[email protected]> wrote:
>> > So I've tried to hack something together, but this is literally a hack:
>> >
>> > https://gist.github.com/0x6e6562/51b0caf1ebb6d06d1ee2
>> >
>> > This seems to work, for some value of work. This means that I can
>> > INSERT and SELECT rows using the custom bindings with generated code.
>> >
>> > But I think I'm missing the point on a number of the abstractions -
>> > this feels like a terrible hack, on many levels.
>> >
>> > Is there a better way?
>>
>> I've taken a second run up at this problem using OffsetDateTime
>> instead of Instant. This allows for the offset to be explicit and it
>> can be down to the application layer to adjust the offset. If the app
>> doesn't adjust the offset from reason, then the worst that can happen
>> is that a non-UTC timestamp is stored, but because the offset is also
>> stored as part of the TIMESTAMP WITH TIME ZONE type, the value is
>> still explicit and not subject to interpretation.
>>
>> Here's the example binding code:
>>
>> https://gist.github.com/0x6e6562/325c0020d1355d28bc08
>>
>> Note that this still uses the hacked up code generator to merge in the
>> TIMESTAMPTZ type information, but this is just a POC right now.
>>
>> The main implementation difference between this variant and the
>> previous is to defer the packing/unpacking to the routines exposed in
>> https://github.com/marschall/threeten-jpa rather than using the built
>> in driver packers. This is because the driver packer from Oracle
>> appears to shell out the server to grab some ref data in order to pack
>> and unpack the Oracle native format. But marschall/threeten-jpa
>> appears to have re-implemented the format as a regular routine. Not
>> sure whether the server based solution is any less performant, but
>> unless the driver is caching the lookups, this might result in a bunch
>> of network round trips. I assume without any evidence to back this up
>> that the reason why the driver takes this approach is to handle the
>> case when the zone map ref data is updated on the server.
>>
>> Note that this approach also links directly to the Oracle library,
>> which means it's a non-portable and non-restributable solution.
>>
>> Having said all of this, am I potentially over thinking the whole problem?
>>
>> --
>> 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.
>
>
> --
> 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.
--
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.
