CODEWITHSUNDEEP
javapsqljooqtimezone-offset
Sören Linnemann
Sören Linnemann

Reputation: 135

How to collect from a "timestamp without time zone" Field to a Java Timestamp without Timezone

My Jooq Query:

dslContext.select(
                timeInterval,
                ifnull(avg(field(FieldName.AVERAGE, Double.class))
                        .cast(Double.class), 0.0))
                .from(channelTimebucketQuery)
                .groupBy(timeInterval)
                .orderBy(timeInterval)
                .collect(Collectors.toMap(Record2::component1, Record2::component2));

returns a Timestamp with a Timezone attached to it and a double enter image description here

But my Profiler says it should return a Timestamp without timezone.

select pg_typeof(time_bucket) from (

    select "alias_67258973"."time_bucket", coalesce(cast(avg(average) as double precision), 0.0) from (select "public"."time_bucket_gapfill"("bucket_width" := cast("public"."create_interval"("seconds" := 43200) as "pg_catalog"."interval"), "ts" := cast("public"."testTable"."time" as timestamp), "start" := cast(null as timestamp), "finish" := cast(null as timestamp)) as "time_bucket", avg("public"."testTable"."average") as "average" from "testTable" where ("public"."testTable"."device" in ('702088'  ) and "public"."testTable"."time" >= timestamp '2020-02-10 13:57:28.2212375' and "public"."testTable"."time" <= timestamp '2020-02-24 13:57:28.2222399') group by time_bucket) as "alias_67258973" group by "alias_67258973"."time_bucket" order by "alias_67258973"."time_bucket"
        ) as x;

enter image description here

Where does the Timezone come from? How do i get the timezone to + 0000

Upvotes: 2

Views: 3914

Answers (1)

Lukas Eder
Lukas Eder

Reputation: 220952

The default JDBC type for the SQL type TIMESTAMP WITHOUT TIME ZONE (or just TIMESTAMP) is java.sql.Timestamp. For historical reasons, which we all regret, java.sql.Timestamp extends java.util.Date, which models a TIMESTAMP WITHOUT TIME ZONE by associating your JVM time zone (i.e. TimeZone.getDefault()) with a unix timestamp.

A much better representation of the SQL TIMESTAMP WITHOUT TIME ZONE data type is java.time.LocalDateTime, which jOOQ supports as well. More recent versions of jOOQ's code generator will have set <javaTimeTypes>true</javaTimeTypes> to make the JSR-310 types the default.

Nevertheless, despite the confusion from the debugger and the associated implicit time zone in java.sql.Timestamp, the two data types are equivalent to one another, and can be transformed to one another via:

Upvotes: 3

Related Questions