work in progress: timestamp patch - Mailing list pgsql-jdbc

Here's the current state of my changes to use Oid.INVALID to make the
server decide between timestamp and timestamptz. Warning: work in progress.

I haven't fixed CallableStatement.getTimestamp() (for OUT parameters)
yet as it appears to be turning the return value into a Timestamp too
early at the moment .. seems a bit messy to fix.

Can the various protagonists try this patch out and see if it fixes
their problems?

-O
? build.local.properties
Index: org/postgresql/jdbc2/AbstractJdbc2ResultSet.java
===================================================================
RCS file: /usr/local/cvsroot/pgjdbc/pgjdbc/org/postgresql/jdbc2/AbstractJdbc2ResultSet.java,v
retrieving revision 1.75
diff -c -r1.75 AbstractJdbc2ResultSet.java
*** org/postgresql/jdbc2/AbstractJdbc2ResultSet.java    8 Jun 2005 01:44:02 -0000    1.75
--- org/postgresql/jdbc2/AbstractJdbc2ResultSet.java    26 Jul 2005 00:00:23 -0000
***************
*** 139,145 ****
          case Types.TIME:
              return getTime(columnIndex);
          case Types.TIMESTAMP:
!             return getTimestamp(columnIndex);
          case Types.BINARY:
          case Types.VARBINARY:
          case Types.LONGVARBINARY:
--- 139,145 ----
          case Types.TIME:
              return getTime(columnIndex);
          case Types.TIMESTAMP:
!             return getTimestamp(columnIndex, null);
          case Types.BINARY:
          case Types.VARBINARY:
          case Types.LONGVARBINARY:
***************
*** 415,429 ****

      public Timestamp getTimestamp(int i, java.util.Calendar cal) throws SQLException
      {
!         // apply available calendar if there is no timezone information
!         if (cal == null || getPGType(i).endsWith("tz") )
!             return getTimestamp(i);
!         java.util.Date tmp = getTimestamp(i);
!         if (tmp == null)
!             return null;
!     Calendar _cal = (Calendar)cal.clone();
!         _cal = org.postgresql.jdbc2.AbstractJdbc2Statement.changeTime(tmp, _cal, false);
!         return new java.sql.Timestamp(_cal.getTime().getTime());
      }


--- 415,432 ----

      public Timestamp getTimestamp(int i, java.util.Calendar cal) throws SQLException
      {
!         this.checkResultSet(i);
!
!         if (cal == null) {
!             cal = new GregorianCalendar();
!         } else {
!             cal = (Calendar)cal.clone();
!         }
!
!         // If this is actually a timestamptz, the server-provided timezone will override
!         // the one we pass in, which is the desired behaviour. Otherwise, we'll
!         // interpret the timezone-less value in the provided timezone.
!         return TimestampUtils.toTimestamp(cal, getString(i));
      }


***************
*** 2098,2107 ****

      public Timestamp getTimestamp(int columnIndex) throws SQLException
      {
!         this.checkResultSet(columnIndex);
!         if (calendar == null)
!             calendar = new GregorianCalendar();
!         return TimestampUtils.toTimestamp(calendar, getString(columnIndex));
      }

      public InputStream getAsciiStream(int columnIndex) throws SQLException
--- 2101,2107 ----

      public Timestamp getTimestamp(int columnIndex) throws SQLException
      {
!         return getTimestamp(columnIndex, null);
      }

      public InputStream getAsciiStream(int columnIndex) throws SQLException
***************
*** 2261,2267 ****

      public Timestamp getTimestamp(String columnName) throws SQLException
      {
!         return getTimestamp(findColumn(columnName));
      }

      public InputStream getAsciiStream(String columnName) throws SQLException
--- 2261,2267 ----

      public Timestamp getTimestamp(String columnName) throws SQLException
      {
!         return getTimestamp(findColumn(columnName), null);
      }

      public InputStream getAsciiStream(String columnName) throws SQLException
Index: org/postgresql/jdbc2/AbstractJdbc2Statement.java
===================================================================
RCS file: /usr/local/cvsroot/pgjdbc/pgjdbc/org/postgresql/jdbc2/AbstractJdbc2Statement.java,v
retrieving revision 1.79
diff -c -r1.79 AbstractJdbc2Statement.java
*** org/postgresql/jdbc2/AbstractJdbc2Statement.java    8 Jul 2005 17:38:29 -0000    1.79
--- org/postgresql/jdbc2/AbstractJdbc2Statement.java    26 Jul 2005 00:00:31 -0000
***************
*** 1301,1317 ****
       */
      public void setTimestamp(int parameterIndex, Timestamp x) throws SQLException
      {
!         checkClosed();
!         if (null == x)
!         {
!             setNull(parameterIndex, Types.TIMESTAMP);
!         }
!         else
!         {
!             if (calendar == null)
!                 calendar = new GregorianCalendar();
!             bindString(parameterIndex, TimestampUtils.toString(sbuf, calendar, x), Oid.TIMESTAMPTZ);
!         }
      }

      private void setCharacterStreamPost71(int parameterIndex, InputStream x, int length, String encoding) throws
SQLException
--- 1301,1307 ----
       */
      public void setTimestamp(int parameterIndex, Timestamp x) throws SQLException
      {
!         setTimestamp(parameterIndex, x, null);
      }

      private void setCharacterStreamPost71(int parameterIndex, InputStream x, int length, String encoding) throws
SQLException
***************
*** 2886,2899 ****
      public void setTimestamp(int i, Timestamp t, java.util.Calendar cal) throws SQLException
      {
          checkClosed();
!         if (cal == null)
!             setTimestamp(i, t);
!         else
!         {
!             Calendar _cal = (Calendar)cal.clone();
!             _cal = changeTime(t, _cal, true);
!             setTimestamp(i, new java.sql.Timestamp(_cal.getTime().getTime()));
          }
      }

      // ** JDBC 2 Extensions for CallableStatement**
--- 2876,2926 ----
      public void setTimestamp(int i, Timestamp t, java.util.Calendar cal) throws SQLException
      {
          checkClosed();
!
!         if (t == null) {
!             setNull(i, Types.TIMESTAMP);
!             return;
          }
+
+         if (cal == null) {
+             if (calendar == null)
+                 calendar = new GregorianCalendar();
+             cal = calendar;
+         } else {
+             cal = (Calendar)cal.clone();
+         }
+
+         // Use INVALID as a compromise to get both TIMESTAMP and TIMESTAMPTZ working.
+         // This is because you get this in a +1300 timezone:
+         //
+         // template1=# select '2005-01-01 15:00:00 +1000'::timestamptz;
+         //       timestamptz
+         // ------------------------
+         //  2005-01-01 18:00:00+13
+         // (1 row)
+
+         // template1=# select '2005-01-01 15:00:00 +1000'::timestamp;
+         //       timestamp
+         // ---------------------
+         //  2005-01-01 15:00:00
+         // (1 row)
+
+         // template1=# select '2005-01-01 15:00:00 +1000'::timestamptz::timestamp;
+         //       timestamp
+         // ---------------------
+         //  2005-01-01 18:00:00
+         // (1 row)
+
+         // So we want to avoid doing a timestamptz -> timestamp conversion, as that
+         // will first convert the timestamptz to an equivalent time in the server's
+         // timezone (+1300, above), then turn it into a timestamp with the "wrong"
+         // time compared to the string we originally provided. But going straight
+         // to timestamp is OK as the input parser for timestamp just throws away
+         // the timezone part entirely. Since we don't know ahead of time what type
+         // we're actually dealing with, INVALID seems the lesser evil, even if it
+         // does give more scope for type-mismatch errors being silently hidden.
+
+         bindString(i, TimestampUtils.toString(sbuf, cal, t), Oid.INVALID); // Let the server infer the right type.
      }

      // ** JDBC 2 Extensions for CallableStatement**
Index: org/postgresql/jdbc2/TimestampUtils.java
===================================================================
RCS file: /usr/local/cvsroot/pgjdbc/pgjdbc/org/postgresql/jdbc2/TimestampUtils.java,v
retrieving revision 1.13
diff -c -r1.13 TimestampUtils.java
*** org/postgresql/jdbc2/TimestampUtils.java    15 Feb 2005 08:31:47 -0000    1.13
--- org/postgresql/jdbc2/TimestampUtils.java    26 Jul 2005 00:00:32 -0000
***************
*** 31,37 ****
       * Load date/time information into the provided calendar
       * returning the fractional seconds.
       */
!     private static int loadCalendar(GregorianCalendar cal, String s, String type) throws SQLException {
          int slen = s.length();

          // Zero out all the fields.
--- 31,37 ----
       * Load date/time information into the provided calendar
       * returning the fractional seconds.
       */
!     private static int loadCalendar(Calendar cal, String s, String type) throws SQLException {
          int slen = s.length();

          // Zero out all the fields.
***************
*** 167,173 ****
      *
      * @throws SQLException if there is a problem parsing s.
      **/
!     public static Timestamp toTimestamp(GregorianCalendar cal, String s) throws SQLException
      {
          if (s == null)
              return null;
--- 167,173 ----
      *
      * @throws SQLException if there is a problem parsing s.
      **/
!     public static Timestamp toTimestamp(Calendar cal, String s) throws SQLException
      {
          if (s == null)
              return null;
***************
*** 184,195 ****
          }

          synchronized(cal) {
-             cal.set(Calendar.ZONE_OFFSET, 0);
-             cal.set(Calendar.DST_OFFSET, 0);
              int nanos = loadCalendar(cal, s, "timestamp");

              Timestamp result = new Timestamp(cal.getTime().getTime());
              result.setNanos(nanos);
              return result;
          }
      }
--- 184,194 ----
          }

          synchronized(cal) {
              int nanos = loadCalendar(cal, s, "timestamp");

              Timestamp result = new Timestamp(cal.getTime().getTime());
              result.setNanos(nanos);
+
              return result;
          }
      }
***************
*** 254,260 ****
          }
      }

!     public static String toString(StringBuffer sbuf, GregorianCalendar cal, Timestamp x) {
          synchronized(sbuf) {
              synchronized(cal) {
                  cal.setTime(x);
--- 253,259 ----
          }
      }

!     public static String toString(StringBuffer sbuf, Calendar cal, Timestamp x) {
          synchronized(sbuf) {
              synchronized(cal) {
                  cal.setTime(x);
***************
*** 276,282 ****
          }
      }

!     public static String toString(StringBuffer sbuf, GregorianCalendar cal, Date x) {
          synchronized(sbuf) {
              synchronized(cal) {
                  cal.setTime(x);
--- 275,281 ----
          }
      }

!     public static String toString(StringBuffer sbuf, Calendar cal, Date x) {
          synchronized(sbuf) {
              synchronized(cal) {
                  cal.setTime(x);
***************
*** 295,301 ****
          }
      }

!     public static String toString(StringBuffer sbuf, GregorianCalendar cal, Time x) {
          synchronized(sbuf) {
              synchronized(cal) {
                  cal.setTime(x);
--- 294,300 ----
          }
      }

!     public static String toString(StringBuffer sbuf, Calendar cal, Time x) {
          synchronized(sbuf) {
              synchronized(cal) {
                  cal.setTime(x);