Re: TIMESTAMP value binary transfer problem

You are right it is related to timezones and daylight saving. I haven't
looked through the recent code, but from memory the old code used to use
a date in 1970 (epoch year) to calculate daylight saving offsets, etc
This causes problems if the timezone information has changed between
then and now. I know this was a problem for UK which was experimenting
with daylight savings during 1970 and causes the calculations to be off.
I'll dig into the code again when I have a minute to find out where this
occurs and if it is in the JDK or the JDBC code.

John
On 26/10/2014 21:51, Vladimir Crnčić (Infoart d.o.o.) wrote:
>
> Well, the server and the client are on the same development Windows 7
> machine. I've done some testing using class in attachment and found
> out that results depend on time zone and daylight saving settings, in
> some zones results are always correct, in some are correct with
> daylight saving turned off, in some with daylight saving turned on,
> and so on. Obviously there's some problem with incoming binary data
> or time zone compensation in TimestampUtils.toTimestampBin()
>
> if (!timestamptz) {
>
> if (tz == null) {
>
> tz = defaultTz;
>
> }
>
> millis -= tz.getOffset(millis);
>
> }
>
> I don't really understand why this compensation is needed in the first
> place, conversion in TimestampUtils.toTimestamp() takes no time zone
> in account with TIMESTAMP type. Anyway, getting different results
> depending on data transfer type is unacceptable.
>
> *From:*John Lister [mailto:john(dot)lister(at)kickstone(dot)com]
> *Sent:* Saturday, October 25, 2014 10:26 PM
> *To:* Dave Cramer; Vladimir Crnčić (Infoart d.o.o.)
> *Cc:* List
> *Subject:* Re: [JDBC] TIMESTAMP value binary transfer problem
>
> There was a problem with some time zones from memory, in the uk for
> example 1970 had an odd daylight saving period and because 1970 is
> used in calculating the time values in the driver this skews the
> results in the driver. Using the current year fixes it when
> calculating times.
>
> Maybe something to check.
>
> On 25 October 2014 19:21:25 GMT+01:00, Dave Cramer <pg(at)fastcrypt(dot)com
> <mailto:pg(at)fastcrypt(dot)com>> wrote:
>
> This is the same as https://github.com/pgjdbc/pgjdbc/pull/133
>
> So what I am wondering is why a small number of people see this
> and the rest are fine ???
>
> What is unique about your environment. Are the server and the
> client on the same timezone ?
>
>
> Dave Cramer
>
> dave.cramer(at)credativ(dot)ca
> http://www.credativ.ca
>
> On 22 October 2014 14:03, Vladimir Crnčić (Infoart d.o.o.)
> <vcrncic(at)infoart(dot)hr <mailto:vcrncic(at)infoart(dot)hr>> wrote:
>
> There is a problem with JDBS driver interpretation of
> "timestamp without time zone" values when binary transfer is
> on. When binary transfer is disabled for TIMESTAMP columns
> (binaryTransferDisable=TIMESTAMP or binaryTransfer=false)
> problem does not occur.
>
> database version: 9.1.1
>
> driver version: 9.2, 9.3
>
> BINARY TRANSFER:
>
> ----------------
>
> database value (TIMESTAMP): 2014-10-26 01:00:04
>
> bytes (AbstractJdbc2Resultset): [0, 1, -87, 71,
> -77, -6, 45, 0]
>
> long value (TimestampUtils): 467600404000000
>
> java timestamp (TimestampUtils): 2014-10-26
> 02:00:04.0 (not OK)
>
> TEXT TRANSFER:
>
> --------------
>
> database value (TIMESTAMP):
> 2014-10-26 01:00:04
>
> string value (AbstractJdbc2Resultset): 2014-10-26 01:00:04
>
> java timestamp (TimestampUtils):
> 2014-10-26 01:00:04.0 (OK)
>
>
> --
> Sent from my Android device with K-9 Mail. Please excuse my brevity.
>
>
>