Somewhat of a follow up to a dated post. I'm trying to use Jooq Intervals with postgres. Is there a better pattern to use besides this one:
.where(TPAS.INTEGRATION_LOG.EXECUTION_DATE.eq(field(“current_date - interval ‘1 days’“, LocalDateTime.class))) The raw SQL would be: log.execution_date::date = current_date - interval '1 days' On Thu, May 31, 2018 at 12:49 AM Lukas Eder <[email protected]> wrote: > Hi Samir, > > jOOQ currently doesn't support this syntax, I'm afraid, so using "plain > SQL templating" is the best you can do. > > Thanks, > Lukas > > 2018-05-30 1:47 GMT+02:00 Samir Faci <[email protected]>: > >> I'm trying to run the following query in jooq. >> >> >> select * from tpas.edt_task where status = 'ERROR' and (source_date_time at >> TIME ZONE source_time_zone)::DATE = current_date - integer '1' >> >> >> >> To translate that into jooq the best I could find was something along >> these lines. >> >> >> dslContext.selectFrom(TPAS.EDT_TASK) >> .where(TPAS.EDT_TASK.STATUS.eq("ERROR")) >> .and(field("(source_date_time at TIME ZONE >> source_time_zone)::DATE", LocalDate.class) >> .eq(DSL.currentLocalDate().sub(1))) >> .fetch() >> .forEach(record -> DBHelper.constructMap(record, columns, >> results)); >> >> >> >> >> The generated SQL looks something along these lines. >> >> >> select >> tpas.edt_task.id, >> tpas.edt_task.tpasns_id, >> tpas.edt_task.advertiser_id, >> tpas.edt_task.data_source, >> tpas.edt_task.log_type, >> tpas.edt_task.source_uri, >> tpas.edt_task.source_date, >> tpas.edt_task.source_size, >> tpas.edt_task.status, >> tpas.edt_task.state, >> tpas.edt_task.start_time, >> tpas.edt_task.end_time, >> tpas.edt_task.executed_by, >> tpas.edt_task.parent_id, >> tpas.edt_task.part_num, >> tpas.edt_task.records_invalid, >> tpas.edt_task.records_read, >> tpas.edt_task.submission_time, >> tpas.edt_task.submitted_by, >> tpas.edt_task.source_time, >> tpas.edt_task.config_pk, >> tpas.edt_task.source_time_zone, >> tpas.edt_task.source_date_timefrom tpas.edt_taskwhere >> (tpas.edt_task.status = 'ERROR' and >> (source_date_time at TIME ZONE source_time_zone) :: DATE = >> (current_date + (-(1) || ' day') :: interval)) >> >> >> >> >> >> Is there an API i can invoke that wouldn't require me to use a >> field("....") notation? also the syntax looks a bit goofy though I think >> it's functionally equivalent. >> >> >> >> >> -- >> Thank you >> Samir Faci >> https://keybase.io/csgeek >> >> -- >> 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. > -- Thank you Samir Faci https://keybase.io/csgeek -- 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/CAJ7OkQ81-my0AMT5gFX4HO%3DR2myRf1Nurp%2B%3D4N6sMnBmY7Bpmg%40mail.gmail.com.
