Time Zone Casting Postgres

[Samir Faci]

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_time
from tpas.edt_task
where (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

[Lukas Eder]

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

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

[Samir Faci]

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'  

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.

--
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.

[Lukas Eder]

Hi Samir,

Use DSL.currentDate().minus(1)

Thanks,

Lukas

To view this discussion on the web visit https://groups.google.com/d/msgid/jooq-user/CAJ7OkQ81-my0AMT5gFX4HO%3DR2myRf1Nurp%2B%3D4N6sMnBmY7Bpmg%40mail.gmail.com.

[Samir Faci]

Haha, nice much easier. 

To view this discussion on the web visit https://groups.google.com/d/msgid/jooq-user/CAB4ELO6Xn6bnkirdSKxK4a82e-BQGB4kCv69MySDDXAWX0fgrQ%40mail.gmail.com.