Обсуждение: Confusion about JDBC + TIME WITHOUT TIME ZONE

I have a table that has a TIME WITHOUT TIME ZONE column.

When I try to insert via JDBC, the time is always adjusted for the
JVM's time zone. This behavior seems wrong to me. For example:

// Java code:
preparedStatement.setTime(1, Time.valueOf("00:00:10"));

Then, watching the PostgreSQL logs, I see that the value supplied
for parameter #1 is '05:00:10'.

Since my TZ is US Eastern, it appears as if JDBC is adjusting for
the timezone. I don't understand why it would do so, as the whole
point is that TIME WITHOUT TIME ZONE doesn't care, record, or
do anything else with or about the TZ.

To me, this feels like a bug. I'm certainly open to someone explaining
to me why this is the correct behavior, so I can stop understanding
it wrong (should that be the case) but that would have to include an
understanding of how to get the desired behavior. That behavior is
that the time passed in is stored without caring about its time zone.

--
Bill Moran

On Sun, 24 Jan 2016 19:32:33 -0500
Dave Cramer <pg@fastcrypt.com> wrote:

> Well .... timezones in JDBC are certainly a thorny issue. First off there
> is no JDBC allowance for any date/time object with out time zones.

That sentence explained it. Thanks.

--
Bill Moran

On 25.01.16 01:00, Bill Moran wrote:
>
> I have a table that has a TIME WITHOUT TIME ZONE column.
>
> When I try to insert via JDBC, the time is always adjusted for the
> JVM's time zone. This behavior seems wrong to me. For example:
>
> // Java code:
> preparedStatement.setTime(1, Time.valueOf("00:00:10"));
>
> Then, watching the PostgreSQL logs, I see that the value supplied
> for parameter #1 is '05:00:10'.

I am more leaning towards a bug. Can you try the following on a
TIMESTAMP WITHOUT TIME ZONE column?

preparedStatement.setTimestamp(1, java.sql.Timestamp.valueOf("2016-01-25
00:00:10"));

If in that case the time (of the timestamp) stored is actually
'00:00:10' then I would argue for a bug. The reasoning being:

The java.sql date and time classes (Date, Time and Timestamp) are to be
interpreted in the JVM default time zone in the case of WITHOUT TIME
ZONE database types.

For example:
  - For a TIMESTAMP WITHOUT TIME ZONE what in SQL is '2016-01-25
00:00:10' will be in Java java.sql.Timestamp 2016-01-25 00:00:10 in the
JVM default time zone
  - For a DATE WITHOUT TIME ZONE what in SQL is '2016-01-25' will be
java.sql.Date 2016-01-25 00:00:00 in the JVM default time zone (the time
is added because java.util.Date instance implicitly always have a time)

If the agree on this then it would make sense that
  - For a TIME WITHOUT TIME ZONE what in SQL is '00:00:10' will be
java.sql.Time 1970-01-01 00:00:10 in the JVM default time zone (the date
is added because java.util.Date instance implicitly always have a date
and in this case 1970-01-01 is specified)


Cheers
Philippe

Bill>When I try to insert via JDBC, the time is always adjusted for the
Bill>JVM's time zone. This behavior seems wrong to me

Bill>// Java code:
Bill>preparedStatement.setTime(1, Time.valueOf("00:00:10"));

A no-brain answer is: you do not provide the time zone, thus the
driver uses "current time zone".
What's wrong with that?

Note: java.sql.Time does not store "dd, hh, mm" in a separate fields.
It always converts to "milliseconds since...", thus it is doomed to
run into timezones.

Have you tried org.postgresql.jdbc.PgPreparedStatement#setTime(int,
java.sql.Time, java.util.Calendar)?

Alternative solution would be to use java.time.LocalTime -- it could
be easier to reason about.

More specific example would help to tell if the behavior is expected or not.
Can you share that?

Vladimir