From: | Rene Pijlman <rene(at)lab(dot)applinet(dot)nl> |
---|---|
To: | pgsql-jdbc(at)postgresql(dot)org |
Subject: | Funny timezone shift causes failure in test suite |
Date: | 2001-11-04 17:44:22 |
Message-ID: | ljtauts2suop8dmdltlr73g2hscri1l9t4@4ax.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers pgsql-jdbc |
The JDBC driver's test suite with current CVS still has one
failure in the TimestampTest. This is with Liam's fixes of a
couple of weeks ago already applied.
I did some debugging and tracing and I have a hard time
explaining what's going on. Perhaps someone can help me out
here.
Below is a detailed transcript of what's happening in the
relevant parts of testGetTimestamp() and testSetTimestamp().
Both client and server were running in the CET (+1:00) timezone.
Test cases 1-3 construct a SQL string with a hard coded date
without a timezone indication, so conversion from localtime to
UTC is done by the backend. Test cases 4-6 go through
Statement.setTimestamp() which converts to UTC in the driver.
The funny thing is that test cases 1 and 2/3 use the same code,
while 1 succeeds and 2 and 3 fail. The only difference appears
to be the actual date used in the test. The explanation may be
in test cases 5 and 6, which succeed with the same dates but
with different code. For some reason, the 1970 date gets a 2
hour time shift from CET (+1) to UTC, while the 1950 date gets a
1 hour time shift as I expected.
So it appears that the time shift algorithm in the backend
differs from the time shift algorithm used in setTimestamp() in
the driver. The driver gives the 1970 date a different time
shift than the 1950 date, whereas the backend treats them both
the same.
This is the mapping table:
Timestamp in CET (+1) In UTC
Backend 1950-02-07 15:00:00 1950-02-07 14:00:00.0
1970-06-02 07:13:00 1970-06-02 06:13:00.0
^^
Driver 1950-02-07 15:00:00.0 1950-02-07 14:00:00.0
1970-06-02 08:13:00.0 1970-06-02 06:13:00.0
^^
Does anyone understand why this is happening and which of the
two algorithms is correct?
Test case 1: passes
-------------------
testGetTimestamp():
stmt.executeUpdate(JDBC2Tests.insertSQL("testtimestamp","'1950-02-07
15:00:00'"))
Sends to the backend: INSERT INTO testtimestamp VALUES
('1950-02-07 15:00:00')
Backend returns: 1950-02-07 15:00:00+01
Matches: getTimestamp(1950, 2, 7, 15, 0, 0, 0)
Test case 2: fails
------------------
testGetTimestamp():
stmt.executeUpdate(JDBC2Tests.insertSQL("testtimestamp",
"'"+getTimestamp(1970, 6, 2, 8, 13, 0, 0).toString() + "'"))
Sends to the backend: INSERT INTO testtimestamp VALUES
('1970-06-02 08:13:00.0')
Backend returns: 1970-06-02 08:13:00+01
Does not match: getTimestamp(1970, 6, 2, 8, 13, 0, 0)
Test case 3: passes
-------------------
testGetTimestamp():
stmt.executeUpdate(JDBC2Tests.insertSQL("testtimestamp","'1970-06-02
08:13:00'")) Sends to the backend: INSERT
INTO testtimestamp VALUES ('1970-06-02 08:13:00')
Backend returns: 1970-06-02 08:13:00+01
Does not match: getTimestamp(1970, 6, 2, 8, 13, 0, 0)
Test case 4: passes
-------------------
pstmt.setTimestamp(1, getTimestamp(1950, 2, 7, 15, 0, 0, 0));
Sends to the backend: INSERT INTO testtimestamp VALUES
('1950-02-07 14:00:00.0+00')
Backend returns: 1950-02-07 15:00:00+01
Matches: getTimestamp(1950, 2, 7, 15, 0, 0, 0)
Test case 5: passes
-------------------
pstmt.setTimestamp(1, getTimestamp(1970, 6, 2, 8, 13, 0, 0));
Sends to the backend: INSERT INTO testtimestamp VALUES
('1970-06-02 06:13:00.0+00')
Backend returns: 1970-06-02 07:13:00+01
Matches: getTimestamp(1970, 6, 2, 8, 13, 0, 0)
Test case 6: passes
-------------------
pstmt.setTimestamp(1, getTimestamp(1970, 6, 2, 8, 13, 0, 0));
Sends to the backend: INSERT INTO testtimestamp VALUES
('1970-06-02 06:13:00.0+00')
Backend returns: 1970-06-02 07:13:00+01
Matches: getTimestamp(1970, 6, 2, 8, 13, 0, 0)
Regards,
René Pijlman <rene(at)lab(dot)applinet(dot)nl>
From | Date | Subject | |
---|---|---|---|
Next Message | Bruce Momjian | 2001-11-04 18:00:17 | Re: [COMMITTERS] pgsql/ oc/src/sgml/client-auth.sgml oc/src/sgm |
Previous Message | Vince Vielhaber | 2001-11-04 17:28:57 | Re: Beta going well |
From | Date | Subject | |
---|---|---|---|
Next Message | Bruce Momjian | 2001-11-04 18:00:17 | Re: [COMMITTERS] pgsql/ oc/src/sgml/client-auth.sgml oc/src/sgm |
Previous Message | Tom Lane | 2001-11-04 16:13:15 | Re: [COMMITTERS] pgsql/ oc/src/sgml/client-auth.sgml oc/src/sgm ... |