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.
