Hello, ... and sorry for the delay
2013/9/5 <[email protected]> > I am using jooq as a query builder to a Postgres table with a column of > type "timestamp with time zone". I see that there is a data type in jooq > that corresponds to this at PostgresDataType.TIMESTAMPWITHTIMEZONE. > However, the generated sql is not dialect specific, instead resulting in > the same sql as any other timestamp. > An example: > > import org.jooq.conf.*; > import org.jooq.impl.DSL; > import org.jooq.*; > import org.jooq.util.postgres.PostgresDataType; > import java.sql.Timestamp; > > public class JooqTest > { > public static String Test() > { > String TABLE_NAME = "tableName"; > Table TABLE = DSL.tableByName(TABLE_NAME); > Field<Timestamp> TIMESTAMP_WITH_TIMEZONE_FIELD = > DSL.fieldByName(PostgresDataType.TIMESTAMPWITHTIMEZONE, TABLE_NAME, > "columnName"); > > Settings settings = new Settings(); > settings.setStatementType(StatementType.STATIC_STATEMENT); // > Instead of prepared statements, we want SQL strings we can directly execute. > settings.setRenderNameStyle(RenderNameStyle.QUOTED); // Protect > against SQL injection. > DSLContext create = DSL.using(SQLDialect.POSTGRES, settings); > > String sql = create > .select() > .from(TABLE) > .where(TIMESTAMP_WITH_TIMEZONE_FIELD.lessOrEqual(new > Timestamp(System.currentTimeMillis()))) > .getSQL(); > > return sql; > } > } > > The above generates SQL like > select * from "tableName" where "tableName"."columnName" <= timestamp > '2013-09-05 10:24:06.075' > but for Postgres to include the timezone, the SQL needs to be > select * from "tableName" where "tableName"."columnName" <= timestamp with > time zone '2013-09-05 10:24:06.075-06:00' > You're right, that inlining isn't correct. I have registered #2738 for this: https://github.com/jOOQ/jOOQ/issues/2738 > From what I can tell, the format of the value to the right of the <= is > generated based on the base sql data type java.sql.Timezone, not the more > specific Postgres data type. > That assessment is correct. > How can I achieve this? > > As of now I have replaced the where(Condition) with where(String) and > built the condition string myself, but I'd like to know if there is a > better way. > This will work of course, but it may be hard to remember when you write a lot of queries. Another workaround is to write a CustomField<Timestamp> similar to the one I've shown in this thread here: https://groups.google.com/d/msg/jooq-user/kFjZf-v1cwI/OUPq2FSRp4cJ That way, you can always reuse your custom serialisation of the relevant timestamp value. I'm afraid, I don't see a better way before fixing #2738 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 [email protected]. For more options, visit https://groups.google.com/groups/opt_out.
