Hi Deepali, As mentioned in a private email:
I think the reason for this is that your converter isn’t being applied when you write DSL.inline(now). Instead, you should write DSL.inline(now, fhRic.CREATED), for example, in order to re-use the converter attached to your fhRic.CREATED column also with the inline value. Does this solve the problem? Best Regards, Lukas On Wed, Feb 14, 2024 at 5:40 PM deepali sharma <[email protected]> wrote: > hi, > > I am facing below issue. MySQL is datetime(6) and the timezone is UTC and > in java we are using Instant. > We have implemented a custom converter: > > public class CMSDateTimeConverter implements Converter<LocalDateTime, > Instant> { > > private static final long serialVersionUID = 1L; > > @Override > public Instant from(LocalDateTime t) { > return t == null ? null : t.toInstant(ZoneOffset.UTC); > } > > @Override > public LocalDateTime to(Instant u) { > return u == null ? null : LocalDateTime.ofInstant(u, ZoneId.ofOffset("UTC", > ZoneOffset.ofHours(0))); > } > > @Override > public Class<LocalDateTime> fromType() { > return LocalDateTime.class; > } > > @Override > public Class<Instant> toType() { > return Instant.class; > } > } > > This custom converter we are using in the jooq xml as below: > <forcedType> > <userType>java.time.Instant</userType> > <converter>org.jooq.util.CMSDateTimeConverter</converter> > <types>DATETIME.*</types> > </forcedType> > > Now the Table gets autogenerated by this as below: > > public final TableField<AccruedCostAllocationReversalRecord, Integer> > VERSION; > public final TableField<AccruedCostAllocationReversalRecord, Instant> > CREATED; > public final TableField<AccruedCostAllocationReversalRecord, Instant> > LAST_MODIFIED; > > private AccruedCostAllocationReversalTable(Name alias, Table< > AccruedCostAllocationReversalRecord> aliased, Field<?>[] parameters, Condition > where) { > super(alias, (Schema)null, aliased, parameters, DSL.comment(""), > TableOptions.table(), where); > this.ID = createField(DSL.name("id"), SQLDataType.INTEGER.nullable(false > ).identity(true), this, ""); > this.ACCRUED_COST_ALLOCATION_ID = createField(DSL.name( > "accrued_cost_allocation_id"), SQLDataType.INTEGER.nullable(false), this, > ""); > this.EFFECTIVE_DATE = createField(DSL.name("effective_date"), SQLDataType. > LOCALDATETIME(6).nullable(false), this, "", new CMSDateTimeConverter()); > this.INVOICE_CURRENCY = createField(DSL.name("invoice_currency"), > SQLDataType.CHAR(3).nullable(false), this, ""); > this.INVOICE_AMOUNT = createField(DSL.name("invoice_amount"), SQLDataType. > DECIMAL(19, 4).nullable(false), this, ""); > this.INVOICE_PAYMENT_FX_RATE = createField(DSL.name( > "invoice_payment_fx_rate"), SQLDataType.DECIMAL(20, 10).nullable(false), > this, ""); > this.INVOICE_PAYMENT_FX_RATE_PROVIDER = createField(DSL.name( > "invoice_payment_fx_rate_provider"), SQLDataType.VARCHAR(255).nullable( > false), this, ""); > this.PAYMENT_CURRENCY = createField(DSL.name("payment_currency"), > SQLDataType.CHAR(3).nullable(false), this, ""); > this.PAYMENT_AMOUNT = createField(DSL.name("payment_amount"), SQLDataType. > DECIMAL(19, 4).nullable(false), this, ""); > this.PAYMENT_REPORTING_FX_RATE = createField(DSL.name( > "payment_reporting_fx_rate"), SQLDataType.DECIMAL(20, 10).nullable(false), > this, ""); > this.PAYMENT_REPORTING_FX_RATE_PROVIDER = createField(DSL.name( > "payment_reporting_fx_rate_provider"), SQLDataType.VARCHAR(255).nullable( > false), this, ""); > this.REPORTING_CURRENCY = createField(DSL.name("reporting_currency"), > SQLDataType.CHAR(3).nullable(false), this, ""); > this.REPORTING_AMOUNT = createField(DSL.name("reporting_amount"), > SQLDataType.DECIMAL(19, 4).nullable(false), this, ""); > this.REVERSAL_ERP_GL_JOURNAL_ENTRY_ID = createField(DSL.name( > "reversal_erp_gl_journal_entry_id"), SQLDataType.INTEGER, this, ""); > this.VERSION = createField(DSL.name("version"), SQLDataType.INTEGER > .nullable(false), this, ""); > this.CREATED = createField(DSL.name("created"), SQLDataType.LOCALDATETIME( > 6).nullable(false), this, "", new CMSDateTimeConverter()); > this.LAST_MODIFIED = createField(DSL.name("last_modified"), SQLDataType. > LOCALDATETIME(6).nullable(false), this, "", new CMSDateTimeConverter()); > } > > Now i am defining the created and lastmodified as instant as below and > supplying them in sql context and then inserting it in table i am getting > error: > > Instant now = LocalDateTime.ofInstant(Instant.now().truncatedTo(ChronoUnit > .SECONDS), > ZoneId.ofOffset("UTC", ZoneOffset.ofHours(0))).toInstant(ZoneOffset.UTC); > > SelectConditionStep<Record1<Integer>> reversableCostsQuery = > buildReversalQuery(context, invoicePayment); > String tempTableName = createTempTableWithIdField(context, > invoicePayment, reversableCostsQuery); > try { > > SelectHavingConditionStep<Record15<Integer, String, BigDecimal, String, > String, BigDecimal, String, String, > Instant, Integer, Instant, Instant, > BigDecimal, BigDecimal, BigDecimal>> reversalSelect = > context.select(aca.ID, aca.INVOICE_CURRENCY, aca.INVOICE_PAYMENT_FX_RATE, > aca.INVOICE_PAYMENT_FX_RATE_PROVIDER, > aca.PAYMENT_CURRENCY, aca.PAYMENT_REPORTING_FX_RATE, aca. > PAYMENT_REPORTING_FX_RATE_PROVIDER, aca.REPORTING_CURRENCY, > DSL.val(usedEffectiveDate), DSL.val(1), DSL.val(now), DSL.val(now), > invoiceAmountReversalField, paymentAmountReversalField, > reportingAmountReversalField) > .from(joinTempTable(context, aca, aca.ACCRUED_COST_ID, tempTableName)) > .leftOuterJoin(acar).on(acar.ACCRUED_COST_ALLOCATION_ID.eq(aca.ID)) > .groupBy(aca.ID, aca.INVOICE_CURRENCY, aca.INVOICE_PAYMENT_FX_RATE, aca. > INVOICE_PAYMENT_FX_RATE_PROVIDER, > aca.PAYMENT_CURRENCY, aca.PAYMENT_REPORTING_FX_RATE, aca. > PAYMENT_REPORTING_FX_RATE_PROVIDER, aca.REPORTING_CURRENCY, > DSL.val(usedEffectiveDate), DSL.val(1), DSL.val(now), DSL.val(now)) > .having(invoiceAmountReversalField.ne(BigDecimal.ZERO)); > > // insert for both cost-matched ACs and assumed_zero > context.insertInto(acar, acar.ACCRUED_COST_ALLOCATION_ID, acar. > INVOICE_CURRENCY, acar.INVOICE_PAYMENT_FX_RATE, acar. > INVOICE_PAYMENT_FX_RATE_PROVIDER, > acar.PAYMENT_CURRENCY, acar.PAYMENT_REPORTING_FX_RATE, acar. > PAYMENT_REPORTING_FX_RATE_PROVIDER, acar.REPORTING_CURRENCY, > acar.EFFECTIVE_DATE, acar.VERSION, acar.CREATED, acar.LAST_MODIFIED, > acar.INVOICE_AMOUNT, acar.PAYMENT_AMOUNT, acar.REPORTING_AMOUNT) > .select(reversalSelect) > .execute(); > > Below is the query that is getting generated: > insert into `feed_history_reconciled_invoice_cost` (`feed_history_id`, > `reconciled_invoice_cost_id`, `created`, `last_modified`, `version`) > select 3, `reconciled_invoice_cost`.`id`, timestamp with time zone > '2024-02-12 17:11:40.056717925+00:00', > timestamp with time zone '2024-02-12 17:11:40.056717925+00:00', 1 > from `reconciled_invoice_cost` join `invoice_line_item` on ` > invoice_line_item`.`id` = `reconciled_invoice_cost`.`invoice_line_item_id` > join `invoice` on `invoice_line_item`.`invoice_id` = `invoice`.`id` > join `tenant` on `invoice`.`tenant_id` = `tenant`.`id` > join `costing_entity` on `costing_entity`.`id` = > `reconciled_invoice_cost`.`costing_entity_id` > left outer join `costing_entity` as `parentCe` on (`parentCe`.`id` = > `costing_entity`.`parent_costing_entity_id` and `parentCe`.`entity_type` = > ?) > where (`reconciled_invoice_cost`.`cost_type` in (?, ?, ?, ?, ?, ?, ?, ?, > ?, ?, ?, ?, ?, ?, ?, ?) > and `reconciled_invoice_cost`.`retracted` = ? and `invoice`.`status` = ? > and > trim(case when `costing_entity`.`entity_type` = ? then > `parentCe`.`reference_03` else `costing_entity`.`reference_03` end) is not > null > and ((`tenant`.`key` = ? and > `reconciled_invoice_cost`.`erp_profit_center_code` in (?, ?, ?, ?)) or > `tenant`.`key` = ?) > and `invoice_line_item`.`invoice_id` = ?)]; > > Getting below error: > You have an error in your SQL syntax; check the manual that corresponds to > your MySQL server version for the right syntax to use near 'time zone > '2024-02-12 17:11:40.056717925+00:00', timestamp with time zone '2024-' at > line 1 > at org.jooq_3.19.0.MYSQL.debug(Unknown Source) > > Not sure what i am missing have been struggling with this with quite a few > days. Any help will be appreciated. > > Thanks, > Deepali Sharma > On Friday, June 19, 2015 at 3:42:23 PM UTC+5:30 Lukas Eder wrote: > >> 2015-06-18 21:38 GMT+02:00 Garret Wilson <[email protected]>: >> >>> On 6/18/2015 12:04 PM, Lukas Eder wrote: >>> >>>> ... >>>> It will if you're using TIMESTAMP WITH TIME ZONE. >>>> It won't if you're using TIMESTAMP (WITHOUT TIME ZONE) >>>> >>>> However, the linked issue (https://github.com/jOOQ/jOOQ/issues/2738) >>>> still prevails, as jOOQ currently doesn't explicitly support TIMESTAMP WITH >>>> TIME ZONE, thus inlining the timestamp (without timezone) will again >>>> produce wrong results, no matter what data type you're using. >>>> >>> >>> What do you mean by "inlining"? >>> >>> I searched online, and you have something called DSL.inline(). >> >> >> Yes, that's what I meant. DSL.inline() forces a single bind variable to >> be inlined every time. There are also other ways to force bind variables to >> be inlined, e.g. by using StatementType.STATIC_STATEMENT on Settings. >> >> So are you saying that if I use get/set on a record (which is what I care >>> about right now), I'm fine? (I guess I didn't realize that "inlining" is >>> something different.) >> >> >> jOOQ doesn't officially support TIMESTAMP WITH TIME ZONE. jOOQ internally >> does the following: >> >> try (PreparedStatement ps = c.prepareStatement( >> "select" >> + " ?::timestamp," // (the cast is necessary, syntactically in >> some situations) >> + " ?::timestamp," >> + " ?::timestamp with time zone," >> + " ?::timestamp with time zone" >> )) { >> >> ps.setTimestamp(1, new Timestamp(0)); >> ps.setTimestamp(2, new Timestamp(0), >> Calendar.getInstance(TimeZone.getTimeZone("UTC"))); >> ps.setTimestamp(3, new Timestamp(0)); >> ps.setTimestamp(4, new Timestamp(0), >> Calendar.getInstance(TimeZone.getTimeZone("UTC"))); >> >> try (ResultSet rs = ps.executeQuery()) { >> rs.next(); >> >> System.out.println(rs.getTimestamp(1) + " / " + >> rs.getTimestamp(1).getTime()); >> System.out.println(rs.getTimestamp(2, >> Calendar.getInstance(TimeZone.getTimeZone("UTC"))) >> + " / " + rs.getTimestamp(2, >> Calendar.getInstance(TimeZone.getTimeZone("UTC"))).getTime()); >> System.out.println(rs.getTimestamp(3) + " / " + >> rs.getTimestamp(3).getTime()); >> System.out.println(rs.getTimestamp(4, >> Calendar.getInstance(TimeZone.getTimeZone("UTC"))) >> + " / " + rs.getTimestamp(4, >> Calendar.getInstance(TimeZone.getTimeZone("UTC"))).getTime()); >> } >> } >> >> As you can see, jOOQ (like JDBC) will take your timestamp / instant, and >> store it in your local timezone. Which is correct (thie timestamp is >> preserved), but not necessarily what you wanted in the first place (the >> time zone is that of your machine's configuration). Perhaps you wanted to >> store the timestamp / instance in UTC. >> >> Does jOOQ get/set on a record correctly use TIMESTAMP WITH TIME ZONE? >> >> >> jOOQ doesn't officially support TIMESTAMP WITH TIME ZONE, so again. >> Please implement your own Binding to be sure. >> > -- > 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]. > To view this discussion on the web visit > https://groups.google.com/d/msgid/jooq-user/5cb43224-278c-47fc-bfcb-1673cc965361n%40googlegroups.com > <https://groups.google.com/d/msgid/jooq-user/5cb43224-278c-47fc-bfcb-1673cc965361n%40googlegroups.com?utm_medium=email&utm_source=footer> > . > -- 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]. To view this discussion on the web visit https://groups.google.com/d/msgid/jooq-user/CAB4ELO4feaFbLRg1CRHiVFVCTKhR6twM1kGuoSGDxX%3D40rGRbg%40mail.gmail.com.
