Lastest on jOOQ and PostgreSQL TIMESTAMP WITH TIMEZONE.

[Garret Wilson]

I'm no SQL expert but I do know my way around Java times, dates, time zones, and Joda.

I saw several (old) discussions here and elsewhere about jOOQ problems with PostgreSQL TIMESTAMP WITH TIMEZONE, including https://github.com/jOOQ/jOOQ/issues/2738 . I don't know what the latest status is.

I note that jOOQ takes a long absolute time value (which is independent of time zone by nature of its being locked to UTC---and equivalent to Java 8 Instant) to store a Timestamp in a TIMESTAMP WITH TIMEZONE column. When I query the value I get back a Timestamp, which again is equivalent (and convertible to) a Java 8 instant. So where does the time zone come in? Is WITH TIMEZONE really required in the type? And if I am saving and retrieving an absolute Instant equivalent (which is locked to UTC), why does the time zone matter?


Basically I only care about time zones as far as retrieving the exact same absolute time value I started with. The most important question: if I use jOOQ to store a value in a TIMESTAMP WITH TIMEZONE using a Timestamp, and later if I connect from another time zone and request the Timestamp back, will I get the same absolute (UTC-relative) value I put in?

[Garret Wilson]

For example, here is one worry I get from the PostgreSQL documentation:

When a timestamp with time zone value is output, it is always converted from UTC to the current timezone zone, and displayed as local time in that zone. To see the time in another time zone, either change timezone or use the AT TIME ZONE construct (see Section 9.9.3).

I interpret this to mean that, unless jOOQ uses AT TIME ZONE (specifying UTC) when querying the value, the value, even though stored in terms of UTC, would be converted to some arbitrary (depending on the current timezone zone) time zone before being converted to an absolute time value (analogous to Instant). So does jOOQ do the appropriate conversions to ensure that the Timestamp I store and the Timestamp I retrieve will always contain the same absolute time value, regardless of the system or database timezone in use?

[Lukas Eder]

Hi Garret,

The problem with the TIMESTAMP WITH TIMEZONE data type is that it is supported only in JDBC 4.2 onwards (i.e. Java 8). jOOQ 3.7 will start adding formal support for Java 8, so it will be good to finally solve the "timezone" issue thoroughly. Right now, jOOQ doesn't do anything "special", so the behaviour will match that of your JDBC driver (which is usually to take your local timezone).

In the meantime, you could get timezones right by implementing your own data type binding (see http://www.jooq.org/doc/latest/manual/code-generation/custom-data-type-bindings). However, if you don't absolutely *need* to have different timezones in your database, you're usually best off avoiding them and using the older TIMESTAMP WITHOUT TIMEZONE (or just TIMESTAMP) data type. Once you do add support for timezones (apart from display in the UI), you're opening pandora's box on various levels in your application.

--
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 jooq-user+...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[Garret Wilson]

I'm not sure I understand. TIMESTAMP without timezone would always give me a local time, right? I want to store and retrieve an absolute time value---the equivalent to System.currentTimeMillis().

Let me break it down like this: If I construct a Timestamp(System.currentTimeMillis()), that gives me an absolute computer time. Is there no type I can use in PostgreSQL so that if I set(timestamp) a value using jOOQ, and then timestamp=get() on another computer, I can be guaranteed that I will get back the original System.currentTimeMillis()? This is the simplest and most fundamental time representation that exists.

Are you saying that my time zone setting on my JVM or my database will influence the value I retrieve? But that is inappropriate---System.currentTimeMillis() is an absolute time value that should not be modified by time zones (because it is tied to UTC).

I find that absurd!! We have a database that supposedly is one of the most advanced and most standards-compliant, and we can't rely on it to give us back the same absolute time value?? Or is the problem with jOOQ?

Surely I am misinterpreting the situation... (Like I said, I'm not an SQL expert.)

Garret

You received this message because you are subscribed to a topic in the Google Groups "jOOQ User Group" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/jooq-user/TebxZ7dxzn8/unsubscribe.
To unsubscribe from this group and all its topics, send an email to jooq-user+...@googlegroups.com.

[Lukas Eder]

Garret. No one said that date, time and timezones are easy :) There's an amusing piece here, which I recommend reading:

http://infiniteundo.com/post/25326999628/falsehoods-programmers-believe-about-time

And when that's not enough, read also:

http://infiniteundo.com/post/25509354022/more-falsehoods-programmers-believe-about-time

I like the bit about "Unix time is the number of seconds since Jan 1st 1970."... unix time doesn't have a way to represent leap seconds ;)

Back to JDBC. Here's an interesting piece by Mark Rotteveel, the Jaybird developer (Firebird JDBC driver):

http://stackoverflow.com/a/14070771/521799

Mark's explanation can be observed as follows:

    Connection c = getConnection();

    try (PreparedStatement ps = c.prepareStatement(

        "select"

      + "  ?::timestamp,"

      + "  ?::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());

        }

    }

The above program uses all permutations of using timezones and not using timezones in Java and in the DB, and the output is always the same:

1970-01-01 01:00:00.0 / 0

1970-01-01 01:00:00.0 / 0

1970-01-01 01:00:00.0 / 0

1970-01-01 01:00:00.0 / 0

As you can see, in each case, the UTC timestamp 0 was correctly stored and retrieved from the database. My own locale is CET / CEST, which was UTC+1 at Epoch, which is what is getting output on Timestamp.toString().

Things get interesting when you use timestamp literals, both in SQL and/or in Java. If you replace the bind variables as such:

        ps.setTimestamp(1, Timestamp.valueOf("1970-01-01 00:00:00"));

        ps.setTimestamp(2, Timestamp.valueOf("1970-01-01 00:00:00"), Calendar.getInstance(TimeZone.getTimeZone("UTC")));

        ps.setTimestamp(3, Timestamp.valueOf("1970-01-01 00:00:00"));

        ps.setTimestamp(4, Timestamp.valueOf("1970-01-01 00:00:00"), Calendar.getInstance(TimeZone.getTimeZone("UTC")));

This is what I'm getting on my machine

1970-01-01 00:00:00.0 / -3600000

1970-01-01 00:00:00.0 / -3600000

1970-01-01 00:00:00.0 / -3600000

1970-01-01 00:00:00.0 / -3600000

I.e. not Epoch, but the timestamp literal that I sent to the server in the first place. Observe that the four combinations of binding / fetching still always produce the same timestamp.

Let's see what happens if the session writing to the database uses a different timezone (let's assume you're in PST) than the session fetching from the database (I'm using again CET or UTC). I'm running this program:

    try (PreparedStatement ps = c.prepareStatement(

        "select"

      + "  ?::timestamp,"

      + "  ?::timestamp,"

      + "  ?::timestamp with time zone,"

      + "  ?::timestamp with time zone"

    )) {

        ps.setTimestamp(1, new Timestamp(0), Calendar.getInstance(TimeZone.getTimeZone("PST")));

        ps.setTimestamp(2, new Timestamp(0), Calendar.getInstance(TimeZone.getTimeZone("PST")));

        ps.setTimestamp(3, new Timestamp(0), Calendar.getInstance(TimeZone.getTimeZone("PST")));

        ps.setTimestamp(4, new Timestamp(0), Calendar.getInstance(TimeZone.getTimeZone("PST")));

        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());

        }

    }

It yields this output:

1969-12-31 16:00:00.0 / -32400000

1969-12-31 17:00:00.0 / -28800000

1970-01-01 01:00:00.0 / 0

1970-01-01 01:00:00.0 / 0

The first timestamp was Epoch stored as PST (16:00), then the timezone information was removed by the database, which turned Epoch into the local time you had at Epoch (-28800 seconds / -8h). Now, when I'm fetching this time from my own timezone CET, I will still get the local time that you had stored (16:00), but in my timezone, this is no longer -28800 seconds, but -32400 seconds (-9h). Quirky enough?

When we use the TIMESTAMP WITH TIME ZONE data type in the database, the timezone is maintained (PST), and when I fetch the Timestamp value, no matter if using CET or UTC, I will still get Epoch, which was safely stored to the database.

TL;DR:

If the UTC timestamp matters to you, use TIMESTAMP WITH TIMEZONE, but you'll have to implement your own data type Binding (http://www.jooq.org/doc/latest/manual/code-generation/custom-data-type-bindings), because jOOQ currently doesn't support that data type. Once you use your own data type Binding, you can also use Java 8's time API, which better represent these different types than java.sql.Timestamp + the ugly Calendar.

If the local time matters to you, or if you're not operating across time zones, you're fine using TIMESTAMP and jOOQ's Field<Timestamp>.

Cheers,

Lukas

[Garret Wilson]

On 6/18/2015 11:25 AM, Lukas Eder wrote:

Garret. No one said that date, time and timezones are easy :)

I'm fully versed in date, time, and time zones. I have co-written a complex time scheduling program which routed appointments of a customer in one time zone to consultants based upon their local available times in various time zones around the world.

The System.getCurrentTimeMillis() is in fact easy. It has a specification. It is internally tied to UTC. It is represented by java.util.Date and java.time.Instant (Java 8) and org.joda.time.Instant. It should always be identical regardless of what time zone you are in. It is only when you want to convert that absolute instant to some local date that you run into problems. But converting to a local time I can handle. What I want to make sure is that my Date/Instant is stored and retrieved with no modifications---independent of the time zone of my database server or my JVM or whatever. This Date/Instant is at its basis a long integer value---and it never, ever, ever changes!!

Lukas, I'll go read your extensive references, which look entertaining. :) But in the end, all I want to know is that I the following JUnit pseudo-code will work:

final long now=System.getCurrentTimeMillis();
final Timestamp before=new Timestamp(now);
...
//create record
jooqRecord.setTimestamp(before);
...
//change JVM time zone to "foo"
//change database time zone to "bar"
//retrieve record
final Timestamp after=jooqRecord.getTimestamp();
assertEqual(before, after);  //a timestamp should never, ever change!!

I apologize in advance if you already answered that question---I'm going to carefully review what you wrote, because you put a lot of time into writing it. But the last part, "... if you're not operating across time zones, you're fine ...", makes me very wary. ;)

Thanks again---I'll read everything and get back to you.

Garret

[Garret Wilson]

On 6/18/2015 11:45 AM, Garret Wilson wrote:

On 6/18/2015 11:25 AM, Lukas Eder wrote:

Garret. No one said that date, time and timezones are easy :)

I'm fully versed in date, time, and time zones.

More accurately, I would say "well versed". :) "Fully" is a bit much. ;)

...

The System.getCurrentTimeMillis() is in fact easy.

Not to say that there aren't some interesting gotchas, but those aren't relevant to the issue I'm addressing here. The point is that this values doesn't change across time zones.

Garret

[Lukas Eder]

2015-06-18 20:45 GMT+02:00 Garret Wilson <gar...@globalmentor.com>:

On 6/18/2015 11:25 AM, Lukas Eder wrote:

Garret. No one said that date, time and timezones are easy :)

I'm fully versed in date, time, and time zones. I have co-written a complex time scheduling program which routed appointments of a customer in one time zone to consultants based upon their local available times in various time zones around the world.

I wish my mobile phone's calendar was written by someone like you :)

 

The System.getCurrentTimeMillis() is in fact easy. It has a specification. It is internally tied to UTC. It is represented by java.util.Date and java.time.Instant (Java 8) and org.joda.time.Instant. It should always be identical regardless of what time zone you are in. It is only when you want to convert that absolute instant to some local date that you run into problems.

... or when you toString() / "fromString()" it, which is something rather confusing for many people, I suspect.

 

But converting to a local time I can handle. What I want to make sure is that my Date/Instant is stored and retrieved with no modifications---independent of the time zone of my database server or my JVM or whatever. This Date/Instant is at its basis a long integer value---and it never, ever, ever changes!!

Then, use TIMESTAMP WITH TIME ZONE, and implement your data type Binding. We'll probably finally ship this out of the box in jOOQ 3.7.

 

Lukas, I'll go read your extensive references, which look entertaining. :) But in the end, all I want to know is that I the following JUnit pseudo-code will work:

final long now=System.getCurrentTimeMillis();
final Timestamp before=new Timestamp(now);
...
//create record
jooqRecord.setTimestamp(before);
...
//change JVM time zone to "foo"
//change database time zone to "bar"
//retrieve record
final Timestamp after=jooqRecord.getTimestamp();
assertEqual(before, after);  //a timestamp should never, ever change!!

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.

I apologize in advance if you already answered that question---I'm going to carefully review what you wrote, because you put a lot of time into writing it. But the last part, "... if you're not operating across time zones, you're fine ...", makes me very wary. ;)

You should be wary. Most people are oblivious of this issue. Most software is written with the SQL TIMESTAMP data type. And that's mostly fine, as the actual machine writing/reading timestamps usually doesn't change timezones ever. What timezone the end user is in doesn't really matter to this discussion, as that most often takes place on a different layer of the application.

But it's certainly a good reason to be wary. The early Java folks made a lot of interesting default choices. Like the default locale, timezone, encoding, etc...

The System.getCurrentTimeMillis() is in fact easy.

Not to say that there aren't some interesting gotchas, but those aren't relevant to the issue I'm addressing here. The point is that this values doesn't change across time zones. I like it as well. It can (almost) never go wrong. Looking forward to your further findings. Lukas

[Garret Wilson]

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(). 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.) Does jOOQ get/set on a record correctly use
TIMESTAMP WITH TIME ZONE?

Garret

[Lukas Eder]

2015-06-18 21:38 GMT+02:00 Garret Wilson <gar...@globalmentor.com>:

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.

[deepali sharma]

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

[Lukas Eder]

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

--

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 jooq-user+...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/jooq-user/5cb43224-278c-47fc-bfcb-1673cc965361n%40googlegroups.com.