The easiest way to do arithmetic with pure day intervals is to simply
subtract an integer number from your timestamp. For instance:
DSL.currentTimestamp().sub(20);
Rendering the above expression:
import static org.jooq.SQLDialect.POSTGRES;
import static org.jooq.impl.DSL.currentTimestamp;
import static org.jooq.impl.DSL.using;
public class Test {
public static void main(String[] args) {
System.out.println(
using(POSTGRES).renderInlined(currentTimestamp().sub(20))
);
}
}
... yields:
(current_timestamp + (-(20) || ' day')::interval)
Regarding your concrete question:
However the binding doesn't work (presumably because the {0} is in a string
> literal ) and I get the literal '{0} day' in my WHERE clause.
> How do I create a INTERVAL literal using bindings?
That's correct. Template variables can't be put inside of string literals.
So, you have several options.
*Concatenate and cast to interval:*
condition("timestamp >= NOW() - ({0} || ' day')::interval", val(20))
*Use jOOQ's DayToSecond interval data type:*
condition("timestamp >= NOW() - {0}", val(new DayToSecond(20)))
*Use PostgreSQL's make_interval function:*
condition("timestamp >= NOW() - make_interval(days := {0})", val(20))
Pick your poison.
Cheers,
Lukas
2015-08-07 21:35 GMT+02:00 Max Kremer <[email protected]>:
> Hello fellow Jooqers
>
> I'm having trouble creating an interval in my JOOQ query. Here is what I'm
> trying to do, I have a bunch of records with timestamps and I want the ones
> created in the last *n *days.
>
> Here is the SQL to illustrate
>
> SELECT *
> FROM stuff
> WHERE timestamp >= NOW() - INTERVAL '20 days'
>
>
> And below is my JOOQ query in JAVA
>
> d.select( field("date_trunc('hour', timestamp)").as("ts_hourly"),field(
> "id") )
> .from( table("stuff") )
> .where(condition("timestamp >= NOW() - INTERVAL '{0} day' ", val(days)))
> .fetch().stream()
> ...
>
> However the binding doesn't work (presumably because the {0} is in a
> string literal ) and I get the literal '{0} day' in my WHERE clause.
> How do I create a INTERVAL literal using bindings?
>
> Thanks!
> -Max
>
> --
> 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.
