PostgreSQL EXTRACT

[Oleg Oleynik]

Hi All!

In PostgreSQL I can extract from TIMESTAMP in many ways:
century, day, decade, dow, doy, epoch, hour, isodow, isoyear, microseconds, millennium, milliseconds, minute, month, quarter, second, timezone, timezone_hour, timezone_minute, week, year.

But in JOOQ DatePart enum contains only YEAR, MONTH, DAY, HOUR, MINUTE, SECOND.

Enums in Java are final so I can't override DatePart for adding necessary values.

Is there way to extract other values, when I use JOOQ + PostgreSQL?

[Lukas Eder]

Hello,

We have a feature request on the roadmap asking for additional DateParts to be supported:

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

For the time being, please use "plain SQL" as documented here:

http://www.jooq.org/doc/latest/manual/sql-building/plain-sql

For instance:

public static Field<Integer> myExtract(String part, Field<Timestamp> timestamp) {

    return DSL.field("extract({0} from {1})", Integer.class, DSL.keyword(part), timestamp);

}

Hope this helps,

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

[Oleg Oleynik]

This really helps.
But I modified your code to:

 public static Field<Integer> myExtract(String part, Field<Timestamp> timestamp) {

        return DSL.field("extract({0} from {1})", Integer.class, DSL.keyword(part), timestamp).as(part);
 }

because I want to get extracted value from results by alias.

Thank you, Lukas!

понедельник, 8 июня 2015 г., 15:17:14 UTC+4 пользователь Lukas Eder написал:

[Lukas Eder]

Issue #2132 will finally be implemented in jOOQ 3.11 with a lot of support in PostgreSQL and some emulations in other databases, where suitable.