Time Intervals

[Robert DiFalco]

I'm about to do some schema changes to include time intervals (not date-time intervals), but before I go to far I was wondering if JOOQ had anything special to craft queries like this:

select ...

from devices d

where (now() at time zone 'UTC' + make_interval(hours := d.utc_offset_hours))::time 

    BETWEEN time '10:00' AND time '22:00';

I suppose at the very least I could make it a text string like this:

"select ...

from devices d

where (now() at time zone 'UTC' + make_interval(hours := d.utc_offset_hours))::time 

    BETWEEN time ?' AND time ?"

But it would be nice to be able to construct this query in a type-safe way that would detect schema changes. Maybe there is and I just don't know all the depths of JOOQ yet.  

TIA,

R.

[Robert DiFalco]

Also I just created a new column with type INTERVAL for PostgreSQL. The datatype that JOOQ generated was Object. Is that correct? I would have expected a Java Interval or a DayToSecond subclass.

--
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/8QoBlipt72Y/unsubscribe.
To unsubscribe from this group and all its topics, send an email to jooq-user+...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[Lukas Eder]

Hello Robert,

2015-07-17 0:01 GMT+02:00 Robert DiFalco <robert....@gmail.com>:

Also I just created a new column with type INTERVAL for PostgreSQL. The datatype that JOOQ generated was Object. Is that correct? I would have expected a Java Interval or a DayToSecond subclass.

Yes, unfortunately, we don't support that yet

https://github.com/jOOQ/jOOQ/issues/2230

For now, you'll have to create your own kind of data type binding for that:

http://www.jooq.org/doc/latest/manual/sql-building/queryparts/custom-bindings/

On Thu, Jul 16, 2015 at 1:46 PM, Robert DiFalco <robert....@gmail.com> wrote:

I'm about to do some schema changes to include time intervals (not date-time intervals), but before I go to far I was wondering if JOOQ had anything special to craft queries like this:

select ...

from devices d

where (now() at time zone 'UTC' + make_interval(hours := d.utc_offset_hours))::time 

    BETWEEN time '10:00' AND time '22:00';

I suppose at the very least I could make it a text string like this:

"select ...

from devices d

where (now() at time zone 'UTC' + make_interval(hours := d.utc_offset_hours))::time 

    BETWEEN time ?' AND time ?"

But it would be nice to be able to construct this query in a type-safe way that would detect schema changes. Maybe there is and I just don't know all the depths of JOOQ yet.

That interval expression will need to go into your own plain SQL implementation, I suspect. As usual, just wrap up that expression in a template behind a static method. Have you tried that? I'm happy to help with concrete issues that you may encounter

Cheers,

Lukas

[Robert DiFalco]

Thanks Lukas, I haven't played with data type bindings yet, do you think I will be able to create a data type binding that maps between PgInterval and java.time.Duration? That would be ideal.

It would also be nice to be able to created on between java.sql.Time and LocalTime.

R.

--

[Robert DiFalco]

Sorry, one last question. I'm still not sure why if I made a column defined as INTERVAL DAY TO SECOND why doesn't JOOQ generate Java code using they JOOQ type DayToSecond?

[Robert DiFalco]

It's kind of weird because jooq make me setIntervalField as a DayToSecond but I can only getIntervalField as PGInterval. Heh, I cannot standardize on any one because the getters and setters for field take different objects. If I try to set the field with PGInterval (since that is the type that jooq returns for MyRecord#getIntervalField) I get a "Cannot translate null SqlException."

[Lukas Eder]

Hi Robert,

Thank you for your various feedback. Unfortunately, I'm not quite sure what exactly appears to be the additional issue here, but let me guess.

1. jOOQ's code generator currently doesn't support PostgreSQL's INTERVAL data type. I've mentioned https://github.com/jOOQ/jOOQ/issues/2230, which is the feature request to add such support.

2. PostgreSQL's JDBC driver indeed exposes PG* types for vendor-specific types such as INTERVAL. However, it is rather difficult for me right now to asses what kind of thing went wrong from your description. Could you please show everything that you've tried and where exactly things have failed?

Cheers,

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.

[Robert DiFalco]

On #2 it's pretty simple, I added schema like this:

      ALTER TABLE devices ADD COLUMN zone_offset INTERVAL DAY TO SECOND;

I expected JOOQ to generate a DayToSecond.java field for setting and getting but it did not, it created a field with setter and getters for type Object. 

R.