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.lister@kickstone.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@fastcrypt.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@infoart.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.