Got bit yesterday by a bit of stupidity on my part.
Was hoping someone could shed some light, however on what exactly happen so I can close the issue out and move on.

"PostgreSQL 8.1.2 on i386-portbld-freebsd6.0, compiled by GCC cc (GCC) 3.4.4 [FreeBSD] 20050518" (although also had issues with 8.2.X....no reason to believe the postgresql version was a factor)

Freebsd servers were 'patched' with latest zoneinfo port and /etc/localtime was 'correct'.

My servers are all set to central US time zone (America/Chicago)
Java was patched and confirmed with tzupdater.

Weirdest thing. Code that has worked through countless DST switchovers suddenly puked on my feet yesterday.

I narrowed it down to a simple test case where I simply did a "select now() as thedate" from one of my postgresql databases and extracted the results using jdbc.

I output the date/time information beforehand to make sure that the VM knew what timezone and time it should be

System.out.println("Date = " + new java.util.Date());
System.out.println("Calendar = " + Calendar.getInstance());

Date = Sun Mar 11 14:50:30 CDT 2007
Calendar = java.util.GregorianCalendar [time=1173642630395,areFieldsSet=true,areAllFieldsS et=true,lenient=true,zone=sun.util.calendar.ZoneIn fo[id="America/Chicago",offset=-21600000,dstSavings=3600000,useDaylight=true,trans itions=235,lastRule=java.util.SimpleTimeZone[id=America/Chicago,offset=-21600000,dstSavings=3600000,useDaylight=true,start Year=0,startMode=3,startMonth=2,startDay=8,startDa yOfWeek=1,startTime=7200000,startTimeMode=0,endMod e=3,endMonth=10,endDay=1,endDayOfWeek=1,endTime=72 00000,endTimeMode=0]],firstDayOfWeek=1,minimalDaysInFirstWeek=1,ERA=1,Y EAR=2007,MONTH=2,WEEK_OF_YEAR=11,WEEK_OF_MONTH=3,D AY_OF_MONTH=11,DAY_OF_YEAR=70,DAY_OF_WEEK=1,DAY_OF _WEEK_IN_MONTH=2,AM_PM=1,HOUR=2,HOUR_OF_DAY=14,MIN UTE=50,SECOND=30,MILLISECOND=395,ZONE_OFFSET=-21600000,DST_OFFSET=3600000]

All appears well...and then:

String stmt = "select now() as thedate";
ps = con.prepareStatement(stmt);
rs = ps.executeQuery();
if(rs.next()) {
System.out.println("raw date is "+rs.getDate("thedate"));
System.out.println("raw date via timestamp is "+rs.getTimestamp("thedate"));
}


results?

2007-03-11 12:58:53,694 INFO [STDOUT] raw date is 2007-03-10
2007-03-11 12:58:53,694 INFO [STDOUT] raw date via timestamp is 2007-03-11 12:58:53.665499 (yeah...the time is different than the above output...from 2 different runs)

It appears that when calling getDate() on a timestamp field yields a different date than getTimestamp in this instance. I had read somewhere that the JDBC specs say to fall back on a passed calendar or use the JVM timezone to calculate the correct date when using getDate()...and the JVM timezone/date/time was correct, so I thought I should have gotten the correct date results.

After a bit of searching, I determined I wasn't using the jdbc driver that I thought I was. I THOUGHT I was using postgresql-8.1-407.jdbc3.jar. In actuality, I was using postgresql-8.0-314.jdbc3.jar. Oops.

When I switched to the newer driver, all was well.
So the question remains....I didn't see anything in the changelog (http://jdbc.postgresql.org/changes.html) AFTER 8.0.314 related to timestamp/date issue. I know it's like closing the barn door after the cow escaped, but I thought I'd see if anyone had any nuggets of info for me.

Thanks!!




---------------------------------
Need Mail bonding?
Go to the Yahoo! Mail Q&A for great tips from Yahoo! Answers users.