PostgreSQL JDBC - Date problem on Aix jdk1.4.1

Author

Date problem on Aix jdk1.4.1

Byron Nikolaidis

2005-07-27, 8:24 pm

Hey folks,

Got a weird Date related problem on AIX 5.3 with JDK 1.4.1. This
occurs with any of the 8.x drivers, the 7.4 driver works fine.

When I query any table with a regular old Date in it, the first row
that comes back is correct (some Date value with 00:00:00 as the time) ,
however all subsequent rows are advanced by 1 hour (they have a time of
01:00:00) . This happens on any table.

When I run it from some other platform (such as Windows or Linux)
against the same database, the values are all correct.

Anybody seen anything like this on AIX or any other platforms?

Byron

Oliver Jowett

2005-07-27, 8:24 pm

Byron Nikolaidis wrote:

> When I query any table with a regular old Date in it, the first row
> that comes back is correct (some Date value with 00:00:00 as the time) ,
> however all subsequent rows are advanced by 1 hour (they have a time of
> 01:00:00) . This happens on any table.

It's quite weird that it's different on the first row, otherwise I'd say
it was a local JVM timezone issue.

Do you have some test code we can try? Without knowing what the
underlying column's type is or which ResultSet accessor you're using
it's just guesswork.

-O

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

Byron Nikolaidis

2005-07-27, 8:24 pm

Here is a really simple test:

public class DateTest {

public static void main(String[] args) throws Exception {
Class.forName("org.postgresql.Driver");
Connection connection =
DriverManager.getConnection("jdbc:postgresql://host/db");
Statement statement = connection.createStatement();
ResultSet rs = statement.executeQuery("select id, effective from
date_test");
while(rs.next() ) {
Date effective = rs.getDate("effective");
int id = rs.getInt("id");
System.out.println("id="+id+", effective=" +
effective.getTime());
}
connection.close();
}
}

Schema Stuff:

CREATE TABLE date_test
(
effective date,
id int2
)

INSERT INTO date_test VALUES ('2005-07-26', 1);
INSERT INTO date_test VALUES ('2005-07-26', 2);
INSERT INTO date_test VALUES ('2005-07-26', 3);

The output results:

/usr/java14/bin/java -cp "/usr/insight/postgresql.jar:." DateTest

id=1, effective=1122350400
000 <--- correct
id=2, effective=1122354000
000 <--- +1 hour
id=3, effective=1122354000
000 <--- all same from here out

-----Original Message-----
From: Oliver Jowett & #91;mailto:oliver@op
encloud.com]
Sent: Wednesday, July 27, 2005 6:59 PM
To: Byron Nikolaidis
Cc: pgsql-jdbc@postgresql.org
Subject: Re: [JDBC] Date problem on Aix jdk1.4.1

Byron Nikolaidis wrote:

> When I query any table with a regular old Date in it, the first row
> that comes back is correct (some Date value with 00:00:00 as the time)
,
> however all subsequent rows are advanced by 1 hour (they have a time
of
> 01:00:00) . This happens on any table.

It's quite weird that it's different on the first row, otherwise I'd say
it was a local JVM timezone issue.

Do you have some test code we can try? Without knowing what the
underlying column's type is or which ResultSet accessor you're using
it's just guesswork.

-O

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

Oliver Jowett

2005-07-27, 8:24 pm

Byron Nikolaidis wrote:
> Here is a really simple test:

> /usr/java14/bin/java -cp "/usr/insight/postgresql.jar:." DateTest
>
> id=1, effective=1122350400
000 <--- correct
> id=2, effective=1122354000
000 <--- +1 hour
> id=3, effective=1122354000
000 <--- all same from here out

It seems ok under 1.4.2 on Linux, with driver build 310, in NZST:

id=1, effective=1122292800
000
id=2, effective=1122292800
000
id=3, effective=1122292800
000

What timezone is the AIX box in exactly? Can you try a 1.4.2 JVM on there?

The only thing I can think of is some sort of Calendar modification
caused by the first getDate() is carrying over to subsequent calls (the
calendar used is shared between calls), but given that the only thing
that code really touches is the zone/dst offsets, and they're reset each
time, I'm not sure what exactly it would be.

You could try putting something like

System.err.println("calendar: " + cal);

in org.postgresql.jdbc2.TimestampUtils.toDate() around line 253, just
before the return, and see if there are any notable differences between
the first and second calls..

-O

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql
.org so that your
message can get through to the mailing list cleanly

Byron Nikolaidis

2005-07-27, 8:24 pm

Timezone on AIX box is EDT (you can see Calendar below too).

As for the Calendar output, the values on AIX between rows is identical
(except for the time=1122350400000). To compare AIX and Windows, I
see a single difference: on Windows the DAY_OF_YEAR=365 compared with
AIX DAY_OF_YEAR=?, not sure if that means anything?

Can anybody run this test on Aix 5.3, either to confirm they get the
same behavior, or use Java 1.4.2 and verify everything works fine?

AIX:

calendar: id#1
java.util.GregorianCalendar& #91;time=11223504000
00,areFieldsSet=fals
e,areAll
FieldsSet=true,lenie
nt=true,zone=sun.util.calendar.ZoneInfo[id="America/
New_York",offset=- 18000000,dstSavings=
3600000,useDaylight=
true,transitio
ns=139,lastRule=java
.util.SimpleTimeZone[id=America/New_York,offset=-180
00000,dstSavings=360
0000,useDaylight=tru
e,startYear=0,startM
ode=3,startM
onth=3,startDay=1,st
artDayOfWeek=1,start
Time=7200000,startTi
meMode=0,end
Mode=2,endMonth=9,en
dDay=- 1,endDayOfWeek=1,end
Time=7200000,endTime
Mode=0
]],firstDayOfWeek=1,
minimalDaysInFirstWe
ek=1,ERA=1,YEAR=2005
,MONTH=6,WEE
K_OF_YEAR=1,WEEK_OF_
MONTH=5,DAY_OF_MONTH
=26,DAY_OF_YEAR=?,DAY_OF_WEEK=4,
DAY_OF_WEEK_IN_MONTH
=5,AM_PM=1,HOUR=7,HO
UR_OF_DAY=0,MINUTE=0
,SECOND=0,MI
LLISECOND=0,ZONE_OFF
SET=- 18000000,DST_OFFSET=
0]

Windows: (using jdk1.4.1_07)

Calendar: id#1
calendar:
java.util.GregorianCalendar& #91;time=11223504000
00,areFieldsSet=fals
e,areAll
FieldsSet=true,lenie
nt=true,zone=sun.util.calendar.ZoneInfo[id="America/
New_York",offset=- 18000000,dstSavings=
3600000,useDaylight=
true,transitio
ns=235,lastRule=java
.util.SimpleTimeZone[id=America/New_York,offset=-180
00000,dstSavings=360
0000,useDaylight=tru
e,startYear=0,startM
ode=3,startM
onth=3,startDay=1,st
artDayOfWeek=1,start
Time=7200000,startTi
meMode=0,end
Mode=2,endMonth=9,en
dDay=- 1,endDayOfWeek=1,end
Time=7200000,endTime
Mode=0
]],firstDayOfWeek=1,
minimalDaysInFirstWe
ek=1,ERA=1,YEAR=2005
,MONTH=6,WEE
K_OF_YEAR=1,WEEK_OF_
MONTH=5,DAY_OF_MONTH
=26,DAY_OF_YEAR=365,
DAY_OF_WEEK=
4,DAY_OF_WEEK_IN_MON
TH=5,AM_PM=1,HOUR=7,
HOUR_OF_DAY=0,MINUTE
=0,SECOND=0,
MILLISECOND=0,ZONE_O
FFSET=- 18000000,DST_OFFSET=
0]

-----Original Message-----
From: Oliver Jowett & #91;mailto:oliver@op
encloud.com]
Sent: Wednesday, July 27, 2005 7:37 PM
To: Byron Nikolaidis
Cc: pgsql-jdbc@postgresql.org
Subject: Re: [JDBC] Date problem on Aix jdk1.4.1

Byron Nikolaidis wrote:
> Here is a really simple test:

> /usr/java14/bin/java -cp "/usr/insight/postgresql.jar:." DateTest
>
> id=1, effective=1122350400
000 <--- correct
> id=2, effective=1122354000
000 <--- +1 hour
> id=3, effective=1122354000
000 <--- all same from here out

It seems ok under 1.4.2 on Linux, with driver build 310, in NZST:

id=1, effective=1122292800
000
id=2, effective=1122292800
000
id=3, effective=1122292800
000

What timezone is the AIX box in exactly? Can you try a 1.4.2 JVM on
there?

The only thing I can think of is some sort of Calendar modification
caused by the first getDate() is carrying over to subsequent calls (the
calendar used is shared between calls), but given that the only thing
that code really touches is the zone/dst offsets, and they're reset each
time, I'm not sure what exactly it would be.

You could try putting something like

System.err.println("calendar: " + cal);

in org.postgresql.jdbc2.TimestampUtils.toDate() around line 253, just
before the return, and see if there are any notable differences between
the first and second calls..

-O

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

Oliver Jowett

2005-07-27, 8:24 pm

Byron Nikolaidis wrote:
> Timezone on AIX box is EDT (you can see Calendar below too).
>
> As for the Calendar output, the values on AIX between rows is identical
> (except for the time=1122350400000). To compare AIX and Windows, I
> see a single difference: on Windows the DAY_OF_YEAR=365 compared with
> AIX DAY_OF_YEAR=?, not sure if that means anything?

Not sure; 365 is certainly wrong though.

I also see a slight difference in the timezone info (transitions=139 vs.
transitions=235) but I don't think that should cause any difference in
theory.

> Can anybody run this test on Aix 5.3, either to confirm they get the
> same behavior, or use Java 1.4.2 and verify everything works fine?

Hm, I can only guess that it's some difference in Calendar behaviour
between 1.4.1 and 1.4.2 then..

I'll send you a build of the driver with my pending timestamp changes
included -- there are lots of changes in the date parsing area and I'm
interested whether they change the behaviour you see or not.

-O

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql
.org so that your
message can get through to the mailing list cleanly

Byron Nikolaidis

2005-07-28, 3:24 am

Thanks Oliver! The driver you sent me worked correctly on aix!

Is there a plan for an official (or somewhat official) driver release
soon?

Byron

Parsed date '2005-07-26' in zone America/New_York as 2005-07-26 AD
00:00:00 -0400 (millis=112235040000
0)
id=1, effective=1122350400
000
Parsed date '2005-07-26' in zone America/New_York as 2005-07-26 AD
00:00:00 -0400 (millis=112235040000
0)
id=2, effective=1122350400
000
Parsed date '2005-07-26' in zone America/New_York as 2005-07-26 AD
00:00:00 -0400 (millis=112235040000
0)
id=3, effective=1122350400
000

-----Original Message-----
From: Oliver Jowett & #91;mailto:oliver@op
encloud.com]
Sent: Wednesday, July 27, 2005 8:24 PM
To: Byron Nikolaidis
Cc: pgsql-jdbc@postgresql.org
Subject: Re: [JDBC] Date problem on Aix jdk1.4.1

Byron Nikolaidis wrote:
> Timezone on AIX box is EDT (you can see Calendar below too).
>
> As for the Calendar output, the values on AIX between rows is
identical
> (except for the time=1122350400000). To compare AIX and Windows,
I
> see a single difference: on Windows the DAY_OF_YEAR=365 compared with
> AIX DAY_OF_YEAR=?, not sure if that means anything?

Not sure; 365 is certainly wrong though.

I also see a slight difference in the timezone info (transitions=139 vs.
transitions=235) but I don't think that should cause any difference in
theory.

> Can anybody run this test on Aix 5.3, either to confirm they get the
> same behavior, or use Java 1.4.2 and verify everything works fine?

Hm, I can only guess that it's some difference in Calendar behaviour
between 1.4.1 and 1.4.2 then..

I'll send you a build of the driver with my pending timestamp changes
included -- there are lots of changes in the date parsing area and I'm
interested whether they change the behaviour you see or not.

-O

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org

Oliver Jowett

2005-07-28, 3:24 am

Byron Nikolaidis wrote:
> Thanks Oliver! The driver you sent me worked correctly on aix!

That's good to know. I suspect some odd behaviour to do with manually
setting ZONE_OFFSET and DST_OFFSET on a Calendar -- the current driver
meddles with those, but with my changes it doesn't.

> Is there a plan for an official (or somewhat official) driver release
> soon?

All going well I'm planning to commit my changes to CVS HEAD this
weekend, and they'll get picked up in the next official build.

-O

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org

Sponsored Links