SEO

Hi,

I wrote the missing translations for escaped functions timestampadd and
timestampdiff. There are two patchs : one for the code+test and one for
the documentation.

Xavier Poinsard.

Index: jdbc2/AbstractJdbc2DatabaseMetaData.java
================================================== =================
RCS file: /usr/local/cvsroot/pgjdbc/pgjdbc/org/postgresql/jdbc2/AbstractJdbc2DatabaseMetaData.java,v
retrieving revision 1.29
diff -u -r1.29 AbstractJdbc2DatabaseMetaData.java
--- jdbc2/AbstractJdbc2DatabaseMetaData.java 3 Feb 2006 21:10:15 -0000 1.29
+++ jdbc2/AbstractJdbc2DatabaseMetaData.java 22 Mar 2006 08:32:51 -0000
@@ -477,7 +477,8 @@
','+EscapedFunctions.MONTH+
','+EscapedFunctions.MONTHNAME+','+EscapedFunction s.NOW+
','+EscapedFunctions.QUARTER+','+EscapedFunctions. SECOND+
- ','+EscapedFunctions.WEEK+','+EscapedFunctions.YEA R;
+ ','+EscapedFunctions.WEEK+','+EscapedFunctions.YEA R+
+ ','+EscapedFunctions.TIMESTAMPADD+','+EscapedFunct ions.TIMESTAMPDIFF;
}

/*
Index: jdbc2/EscapedFunctions.java
================================================== =================
RCS file: /usr/local/cvsroot/pgjdbc/pgjdbc/org/postgresql/jdbc2/EscapedFunctions.java,v
retrieving revision 1.6
diff -u -r1.6 EscapedFunctions.java
--- jdbc2/EscapedFunctions.java 4 Jun 2005 18:24:08 -0000 1.6
+++ jdbc2/EscapedFunctions.java 22 Mar 2006 08:32:51 -0000
@@ -90,8 +90,23 @@
public final static String SECOND="second";
public final static String WEEK="week";
public final static String YEAR="year";
- // TODO : timestampadd and timestampdiff
+ // for timestampadd and timestampdiff the fractional part of second is not supported
+ // by the backend
+ public final static String TIMESTAMPADD="timestampadd";
+ public final static String TIMESTAMPDIFF="timestampdiff";
+
+ // constants for timestampadd and timestampdiff
+ public final static String SQL_TSI_DAY="SQL_TSI_DAY";
+ public final static String SQL_TSI_FRAC_SECOND="SQL_TSI_FRAC_SECOND";
+ public final static String SQL_TSI_HOUR="SQL_TSI_HOUR";
+ public final static String SQL_TSI_MINUTE="SQL_TSI_MINUTE";
+ public final static String SQL_TSI_MONTH="SQL_TSI_MONTH";
+ public final static String SQL_TSI_QUARTER="SQL_TSI_QUARTER";
+ public final static String SQL_TSI_SECOND="SQL_TSI_SECOND";
+ public final static String SQL_TSI_WEEK="SQL_TSI_WEEK";
+ public final static String SQL_TSI_YEAR="SQL_TSI_YEAR";

+
// system functions
public final static String DATABASE="database";
public final static String IFNULL="ifnull";
@@ -478,6 +493,79 @@
return "extract(year from "+parsedArgs.get(0)+")";
}

+ /** time stamp add */
+ public static String sqltimestampadd(List parsedArgs) throws SQLException{
+ if (parsedArgs.size()!=3){
+ throw new PSQLException(GT.tr("{0} function takes three and only three arguments.","timestampadd"),
+ PSQLState.SYNTAX_ERROR);
+ }
+ StringBuffer buf = new StringBuffer();
+ buf.append("(").append(EscapedFunctions.constantTo Interval(parsedArgs.get(0).toString(),parsedArgs.g et(1).toString()))
+ .append("+").append(parsedArgs.get(2)).append(")") ;
+ return buf.toString();
+ }
+
+ private final static String constantToInterval(String type,String value)throws SQLException{
+ if (SQL_TSI_DAY.equalsIgnoreCase(type))
+ return "'"+value+" day'";
+ else if (SQL_TSI_SECOND.equalsIgnoreCase(type))
+ return "'"+value+" second'";
+ else if (SQL_TSI_HOUR.equalsIgnoreCase(type))
+ return "'"+value+" hour'";
+ else if (SQL_TSI_MINUTE.equalsIgnoreCase(type))
+ return "'"+value+" minute'";
+ else if (SQL_TSI_MONTH.equalsIgnoreCase(type))
+ return "'"+value+" month'";
+ else if (SQL_TSI_QUARTER.equalsIgnoreCase(type))
+ return "'3*"+value+" month'";
+ else if (SQL_TSI_WEEK.equalsIgnoreCase(type))
+ return "'"+value+" week'";
+ else if (SQL_TSI_YEAR.equalsIgnoreCase(type))
+ return "'"+value+" year'";
+ else if (SQL_TSI_FRAC_SECOND.equalsIgnoreCase(type))
+ throw new PSQLException(GT.tr("Interval {0} not yet implemented","SQL_TSI_FRAC_SECOND"),
+ PSQLState.SYNTAX_ERROR);
+ else throw new PSQLException(GT.tr("Interval {0} not yet implemented",type),
+ PSQLState.SYNTAX_ERROR);
+ }
+
+
+ /** time stamp diff */
+ public static String sqltimestampdiff(List parsedArgs) throws SQLException{
+ if (parsedArgs.size()!=3){
+ throw new PSQLException(GT.tr("{0} function takes three and only three arguments.","timestampdiff"),
+ PSQLState.SYNTAX_ERROR);
+ }
+ StringBuffer buf = new StringBuffer();
+ buf.append("extract( ").append(EscapedFunctions.constantToDatePart(pars edArgs.get(0).toString()))
+ .append(" from (").append(parsedArgs.get(2)).append("-").append(parsedArgs.get(1)).append("))");
+ return buf.toString();
+ }
+
+ private final static String constantToDatePart(String type)throws SQLException{
+ if (SQL_TSI_DAY.equalsIgnoreCase(type))
+ return "day";
+ else if (SQL_TSI_SECOND.equalsIgnoreCase(type))
+ return "second";
+ else if (SQL_TSI_HOUR.equalsIgnoreCase(type))
+ return "hour";
+ else if (SQL_TSI_MINUTE.equalsIgnoreCase(type))
+ return "minute";
+ else if (SQL_TSI_MONTH.equalsIgnoreCase(type))
+ return "month";
+ else if (SQL_TSI_QUARTER.equalsIgnoreCase(type))
+ return "quarter";
+ else if (SQL_TSI_WEEK.equalsIgnoreCase(type))
+ return "week";
+ else if (SQL_TSI_YEAR.equalsIgnoreCase(type))
+ return "year";
+ else if (SQL_TSI_FRAC_SECOND.equalsIgnoreCase(type))
+ throw new PSQLException(GT.tr("Interval {0} not yet implemented","SQL_TSI_FRAC_SECOND"),
+ PSQLState.SYNTAX_ERROR);
+ else throw new PSQLException(GT.tr("Interval {0} not yet implemented",type),
+ PSQLState.SYNTAX_ERROR);
+ }
+
/** database translation */
public static String sqldatabase(List parsedArgs) throws SQLException{
if (parsedArgs.size()!=0){
Index: test/jdbc2/StatementTest.java
================================================== =================
RCS file: /usr/local/cvsroot/pgjdbc/pgjdbc/org/postgresql/test/jdbc2/StatementTest.java,v
retrieving revision 1.19
diff -u -r1.19 StatementTest.java
--- test/jdbc2/StatementTest.java 1 Feb 2006 18:52:13 -0000 1.19
+++ test/jdbc2/StatementTest.java 22 Mar 2006 08:32:52 -0000
@@ -297,6 +297,10 @@
assertTrue(rs.next());
// ensure sunday =>1 and monday =>2
assertEquals(2,rs.getInt(5));
+
+ rs = stmt.executeQuery("select {fn timestampdiff(SQL_TSI_DAY,{fn now()},{fn timestampadd(SQL_TSI_DAY,3,{fn now()})})} ");
+ assertTrue(rs.next());
+ assertEquals(3,rs.getInt(1));
}

public void testSystemFunctions() throws SQLException

Index: pgjdbc.xml
================================================== =================
RCS file: /usr/local/cvsroot/pgjdbc/pgjdbc/doc/pgjdbc.xml,v
retrieving revision 1.27
diff -u -r1.27 pgjdbc.xml
--- pgjdbc.xml 24 Nov 2005 06:36:03 -0000 1.27
+++ pgjdbc.xml 22 Mar 2006 08:33:21 -0000
@@ -2040,6 +2040,18 @@
<entry>extract(year from arg1)</entry>
<entry></entry>
</row>
+ <row>
+ <entry>timestampadd(argIntervalType,argCount,argTi meStamp)</entry>
+ <entry>yes</entry>
+ <entry>('(interval according to argIntervalType and argCount)'+argTimeStamp)</entry>
+ <entry>an argIntervalType value of <classname>SQL_TSI_FRAC_SECOND</classname> is not implemented since backend does not support it</entry>
+ </row>
+ <row>
+ <entry>timestampdiff(argIntervalType,argTimeStamp1 ,argTimeStamp2)</entry>
+ <entry>yes</entry>
+ <entry>extract((interval according to argIntervalType) from argTimeStamp2-argTimeStamp1 )</entry>
+ <entry>an argIntervalType value of <classname>SQL_TSI_FRAC_SECOND</classname> is not implemented since backend does not support it</entry>
+ </row>
</tbody>
</tgroup>
</table>


---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

Xavier,

Thanks, any chance we could get a context diff instead of a plain diff ?

Also I looked at it briefly, can you change the if (SQL_TSI_DAY. ....
checks to check for "SQL_TSI" , before you even create the
stringbuffer in sqltimestampdiff, and sqltimestampadd

Ideally it would be good to verify all of the functions in the test .

Dave




Dave
On 22-Mar-06, at 3:38 AM, Xavier Poinsard wrote:

> Hi,
>
> I wrote the missing translations for escaped functions timestampadd
> and
> timestampdiff. There are two patchs : one for the code+test and one
> for
> the documentation.
>
> Xavier Poinsard.
> Index: jdbc2/AbstractJdbc2DatabaseMetaData.java
> ================================================== =================
> RCS file: /usr/local/cvsroot/pgjdbc/pgjdbc/org/postgresql/jdbc2/
> AbstractJdbc2DatabaseMetaData.java,v
> retrieving revision 1.29
> diff -u -r1.29 AbstractJdbc2DatabaseMetaData.java
> --- jdbc2/AbstractJdbc2DatabaseMetaData.java 3 Feb 2006 21:10:15
> -0000 1.29
> +++ jdbc2/AbstractJdbc2DatabaseMetaData.java 22 Mar 2006 08:32:51
> -0000
> @@ -477,7 +477,8 @@
> ','+EscapedFunctions.MONTH+
> ','+EscapedFunctions.MONTHNAME+','+EscapedFunction s.NOW+
> ','+EscapedFunctions.QUARTER+','+EscapedFunctions. SECOND+
> - ','+EscapedFunctions.WEEK+','+EscapedFunctions.YEA R;
> + ','+EscapedFunctions.WEEK+','+EscapedFunctions.YEA R+
> + ','+EscapedFunctions.TIMESTAMPADD
> +','+EscapedFunctions.TIMESTAMPDIFF;
> }
>
> /*
> Index: jdbc2/EscapedFunctions.java
> ================================================== =================
> RCS file: /usr/local/cvsroot/pgjdbc/pgjdbc/org/postgresql/jdbc2/
> EscapedFunctions.java,v
> retrieving revision 1.6
> diff -u -r1.6 EscapedFunctions.java
> --- jdbc2/EscapedFunctions.java 4 Jun 2005 18:24:08 -0000 1.6
> +++ jdbc2/EscapedFunctions.java 22 Mar 2006 08:32:51 -0000
> @@ -90,8 +90,23 @@
> public final static String SECOND="second";
> public final static String WEEK="week";
> public final static String YEAR="year";
> - // TODO : timestampadd and timestampdiff
> + // for timestampadd and timestampdiff the fractional part of
> second is not supported
> + // by the backend
> + public final static String TIMESTAMPADD="timestampadd";
> + public final static String TIMESTAMPDIFF="timestampdiff";
> +
> + // constants for timestampadd and timestampdiff
> + public final static String SQL_TSI_DAY="SQL_TSI_DAY";
> + public final static String
> SQL_TSI_FRAC_SECOND="SQL_TSI_FRAC_SECOND";
> + public final static String SQL_TSI_HOUR="SQL_TSI_HOUR";
> + public final static String SQL_TSI_MINUTE="SQL_TSI_MINUTE";
> + public final static String SQL_TSI_MONTH="SQL_TSI_MONTH";
> + public final static String SQL_TSI_QUARTER="SQL_TSI_QUARTER";
> + public final static String SQL_TSI_SECOND="SQL_TSI_SECOND";
> + public final static String SQL_TSI_WEEK="SQL_TSI_WEEK";
> + public final static String SQL_TSI_YEAR="SQL_TSI_YEAR";
>
> +
> // system functions
> public final static String DATABASE="database";
> public final static String IFNULL="ifnull";
> @@ -478,6 +493,79 @@
> return "extract(year from "+parsedArgs.get(0)+")";
> }
>
> + /** time stamp add */
> + public static String sqltimestampadd(List parsedArgs) throws
> SQLException{
> + if (parsedArgs.size()!=3){
> + throw new PSQLException(GT.tr("{0} function takes
> three and only three arguments.","timestampadd"),
> + PSQLState.SYNTAX_ERROR);
> + }
> + StringBuffer buf = new StringBuffer();
> + buf.append("(").append(EscapedFunctions.constantTo Interval
> (parsedArgs.get(0).toString(),parsedArgs.get(1).to String()))
> + .append("+").append(parsedArgs.get(2)).append(")") ;
> + return buf.toString();
> + }
> +
> + private final static String constantToInterval(String
> type,String value)throws SQLException{
> + if (SQL_TSI_DAY.equalsIgnoreCase(type))
> + return "'"+value+" day'";
> + else if (SQL_TSI_SECOND.equalsIgnoreCase(type))
> + return "'"+value+" second'";
> + else if (SQL_TSI_HOUR.equalsIgnoreCase(type))
> + return "'"+value+" hour'";
> + else if (SQL_TSI_MINUTE.equalsIgnoreCase(type))
> + return "'"+value+" minute'";
> + else if (SQL_TSI_MONTH.equalsIgnoreCase(type))
> + return "'"+value+" month'";
> + else if (SQL_TSI_QUARTER.equalsIgnoreCase(type))
> + return "'3*"+value+" month'";
> + else if (SQL_TSI_WEEK.equalsIgnoreCase(type))
> + return "'"+value+" week'";
> + else if (SQL_TSI_YEAR.equalsIgnoreCase(type))
> + return "'"+value+" year'";
> + else if (SQL_TSI_FRAC_SECOND.equalsIgnoreCase(type))
> + throw new PSQLException(GT.tr("Interval {0} not yet
> implemented","SQL_TSI_FRAC_SECOND"),
> + PSQLState.SYNTAX_ERROR);
> + else throw new PSQLException(GT.tr("Interval {0} not yet
> implemented",type),
> + PSQLState.SYNTAX_ERROR);
> + }
> +
> +
> + /** time stamp diff */
> + public static String sqltimestampdiff(List parsedArgs) throws
> SQLException{
> + if (parsedArgs.size()!=3){
> + throw new PSQLException(GT.tr("{0} function takes
> three and only three arguments.","timestampdiff"),
> + PSQLState.SYNTAX_ERROR);
> + }
> + StringBuffer buf = new StringBuffer();
> + buf.append("extract( ").append
> (EscapedFunctions.constantToDatePart(parsedArgs.ge t(0).toString()))
> + .append(" from (").append(parsedArgs.get(2)).append
> ("-").append(parsedArgs.get(1)).append("))");
> + return buf.toString();
> + }
> +
> + private final static String constantToDatePart(String type)
> throws SQLException{
> + if (SQL_TSI_DAY.equalsIgnoreCase(type))
> + return "day";
> + else if (SQL_TSI_SECOND.equalsIgnoreCase(type))
> + return "second";
> + else if (SQL_TSI_HOUR.equalsIgnoreCase(type))
> + return "hour";
> + else if (SQL_TSI_MINUTE.equalsIgnoreCase(type))
> + return "minute";
> + else if (SQL_TSI_MONTH.equalsIgnoreCase(type))
> + return "month";
> + else if (SQL_TSI_QUARTER.equalsIgnoreCase(type))
> + return "quarter";
> + else if (SQL_TSI_WEEK.equalsIgnoreCase(type))
> + return "week";
> + else if (SQL_TSI_YEAR.equalsIgnoreCase(type))
> + return "year";
> + else if (SQL_TSI_FRAC_SECOND.equalsIgnoreCase(type))
> + throw new PSQLException(GT.tr("Interval {0} not yet
> implemented","SQL_TSI_FRAC_SECOND"),
> + PSQLState.SYNTAX_ERROR);
> + else throw new PSQLException(GT.tr("Interval {0} not yet
> implemented",type),
> + PSQLState.SYNTAX_ERROR);
> + }
> +
> /** database translation */
> public static String sqldatabase(List parsedArgs) throws
> SQLException{
> if (parsedArgs.size()!=0){
> Index: test/jdbc2/StatementTest.java
> ================================================== =================
> RCS file: /usr/local/cvsroot/pgjdbc/pgjdbc/org/postgresql/test/
> jdbc2/StatementTest.java,v
> retrieving revision 1.19
> diff -u -r1.19 StatementTest.java
> --- test/jdbc2/StatementTest.java 1 Feb 2006 18:52:13 -0000 1.19
> +++ test/jdbc2/StatementTest.java 22 Mar 2006 08:32:52 -0000
> @@ -297,6 +297,10 @@
> assertTrue(rs.next());
> // ensure sunday =>1 and monday =>2
> assertEquals(2,rs.getInt(5));
> +
> + rs = stmt.executeQuery("select {fn timestampdiff
> (SQL_TSI_DAY,{fn now()},{fn timestampadd(SQL_TSI_DAY,3,{fn now
> ()})})} ");
> + assertTrue(rs.next());
> + assertEquals(3,rs.getInt(1));
> }
>
> public void testSystemFunctions() throws SQLException
> Index: pgjdbc.xml
> ================================================== =================
> RCS file: /usr/local/cvsroot/pgjdbc/pgjdbc/doc/pgjdbc.xml,v
> retrieving revision 1.27
> diff -u -r1.27 pgjdbc.xml
> --- pgjdbc.xml 24 Nov 2005 06:36:03 -0000 1.27
> +++ pgjdbc.xml 22 Mar 2006 08:33:21 -0000
> @@ -2040,6 +2040,18 @@
> <entry>extract(year from arg1)</entry>
> <entry></entry>
> </row>
> + <row>
> + <entry>timestampadd(argIntervalType,argCount,argTi meStamp)</
> entry>
> + <entry>yes</entry>
> + <entry>('(interval according to argIntervalType and
> argCount)'+argTimeStamp)</entry>
> + <entry>an argIntervalType value of
> <classname>SQL_TSI_FRAC_SECOND</classname> is not implemented since
> backend does not support it</entry>
> + </row>
> + <row>
> + <entry>timestampdiff
> (argIntervalType,argTimeStamp1,argTimeStamp2)</entry>
> + <entry>yes</entry>
> + <entry>extract((interval according to argIntervalType) from
> argTimeStamp2-argTimeStamp1 )</entry>
> + <entry>an argIntervalType value of
> <classname>SQL_TSI_FRAC_SECOND</classname> is not implemented since
> backend does not support it</entry>
> + </row>
> </tbody>
> </tgroup>
> </table>
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 6: explain analyze is your friend


---------------------------(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

Dave Cramer a écrit :
> Xavier,
>
> Thanks, any chance we could get a context diff instead of a plain diff ?

I should be better.

>
> Also I looked at it briefly, can you change the if (SQL_TSI_DAY. ....
> checks to check for "SQL_TSI" , before you even create the stringbuffer
> in sqltimestampdiff, and sqltimestampadd

done.

>
> Ideally it would be good to verify all of the functions in the test .

That's a good point since I found several unexpected problems with the
backend implementation of extract which makes severals SQL_TSI
unavailable for timestampdiff :

select extract(month from interval '92 days');
date_part
-----------
0
(1 row)

test=> select extract(year from interval '900 days');
date_part
-----------
0
(1 row)

test=> select extract(quarter from interval '900 days');
date_part
-----------
1
(1 row)


>
> Dave
>



Index: pgjdbc.xml
================================================== =================
RCS file: /usr/local/cvsroot/pgjdbc/pgjdbc/doc/pgjdbc.xml,v
retrieving revision 1.27
diff -c -r1.27 pgjdbc.xml
*** pgjdbc.xml 24 Nov 2005 06:36:03 -0000 1.27
--- pgjdbc.xml 22 Mar 2006 14:43:27 -0000
***************
*** 2040,2045 ****
--- 2040,2058 ----
<entry>extract(year from arg1)</entry>
<entry></entry>
</row>
+ <row>
+ <entry>timestampadd(argIntervalType,argCount,argTi meStamp)</entry>
+ <entry>yes</entry>
+ <entry>('(interval according to argIntervalType and argCount)'+argTimeStamp)</entry>
+ <entry>an argIntervalType value of <classname>SQL_TSI_FRAC_SECOND</classname> is not implemented since backend does not support it</entry>
+ </row>
+ <row>
+ <entry>timestampdiff(argIntervalType,argTimeStamp1 ,argTimeStamp2)</entry>
+ <entry>yes</entry>
+ <entry>extract((interval according to argIntervalType) from argTimeStamp2-argTimeStamp1 )</entry>
+ <entry>an argIntervalType value of <classname>SQL_TSI_FRAC_SECOND</classname> or <classname>SQL_TSI_WEEK</classname> are not implemented since backend does not support it.
+ You may note that SQL_TSI_MONTH, SQL_TSI_QUARTER, SQL_TSI_YEAR are giving unexpected results since the backend assumes that there are 0 year in 900 days, 1 quarter in 900 days or 0 month in 92 days. </entry>
+ </row>
</tbody>
</tgroup>
</table>

Index: jdbc2/AbstractJdbc2DatabaseMetaData.java
================================================== =================
RCS file: /usr/local/cvsroot/pgjdbc/pgjdbc/org/postgresql/jdbc2/AbstractJdbc2DatabaseMetaData.java,v
retrieving revision 1.29
diff -c -r1.29 AbstractJdbc2DatabaseMetaData.java
*** jdbc2/AbstractJdbc2DatabaseMetaData.java 3 Feb 2006 21:10:15 -0000 1.29
--- jdbc2/AbstractJdbc2DatabaseMetaData.java 22 Mar 2006 14:47:44 -0000
***************
*** 477,483 ****
','+EscapedFunctions.MONTH+
','+EscapedFunctions.MONTHNAME+','+EscapedFunction s.NOW+
','+EscapedFunctions.QUARTER+','+EscapedFunctions. SECOND+
! ','+EscapedFunctions.WEEK+','+EscapedFunctions.YEA R;
}

/*
--- 477,484 ----
','+EscapedFunctions.MONTH+
','+EscapedFunctions.MONTHNAME+','+EscapedFunction s.NOW+
','+EscapedFunctions.QUARTER+','+EscapedFunctions. SECOND+
! ','+EscapedFunctions.WEEK+','+EscapedFunctions.YEA R+
! ','+EscapedFunctions.TIMESTAMPADD+','+EscapedFunct ions.TIMESTAMPDIFF;
}

/*
Index: jdbc2/EscapedFunctions.java
================================================== =================
RCS file: /usr/local/cvsroot/pgjdbc/pgjdbc/org/postgresql/jdbc2/EscapedFunctions.java,v
retrieving revision 1.6
diff -c -r1.6 EscapedFunctions.java
*** jdbc2/EscapedFunctions.java 4 Jun 2005 18:24:08 -0000 1.6
--- jdbc2/EscapedFunctions.java 22 Mar 2006 14:47:44 -0000
***************
*** 90,97 ****
public final static String SECOND="second";
public final static String WEEK="week";
public final static String YEAR="year";
! // TODO : timestampadd and timestampdiff

// system functions
public final static String DATABASE="database";
public final static String IFNULL="ifnull";
--- 90,112 ----
public final static String SECOND="second";
public final static String WEEK="week";
public final static String YEAR="year";
! // for timestampadd and timestampdiff the fractional part of second is not supported
! // by the backend
! public final static String TIMESTAMPADD="timestampadd";
! public final static String TIMESTAMPDIFF="timestampdiff";
!
! // constants for timestampadd and timestampdiff
! public final static String SQL_TSI_DAY="SQL_TSI_DAY";
! public final static String SQL_TSI_FRAC_SECOND="SQL_TSI_FRAC_SECOND";
! public final static String SQL_TSI_HOUR="SQL_TSI_HOUR";
! public final static String SQL_TSI_MINUTE="SQL_TSI_MINUTE";
! public final static String SQL_TSI_MONTH="SQL_TSI_MONTH";
! public final static String SQL_TSI_QUARTER="SQL_TSI_QUARTER";
! public final static String SQL_TSI_SECOND="SQL_TSI_SECOND";
! public final static String SQL_TSI_WEEK="SQL_TSI_WEEK";
! public final static String SQL_TSI_YEAR="SQL_TSI_YEAR";

+
// system functions
public final static String DATABASE="database";
public final static String IFNULL="ifnull";
***************
*** 478,483 ****
--- 493,573 ----
return "extract(year from "+parsedArgs.get(0)+")";
}

+ /** time stamp add */
+ public static String sqltimestampadd(List parsedArgs) throws SQLException{
+ if (parsedArgs.size()!=3){
+ throw new PSQLException(GT.tr("{0} function takes three and only three arguments.","timestampadd"),
+ PSQLState.SYNTAX_ERROR);
+ }
+ String interval = EscapedFunctions.constantToInterval(parsedArgs.get (0).toString(),parsedArgs.get(1).toString());
+ StringBuffer buf = new StringBuffer();
+ buf.append("(interval ").append(interval)
+ .append("+").append(parsedArgs.get(2)).append(")") ;
+ return buf.toString();
+ }
+
+ private final static String constantToInterval(String type,String value)throws SQLException{
+ if (SQL_TSI_DAY.equalsIgnoreCase(type))
+ return "'"+value+" day'";
+ else if (SQL_TSI_SECOND.equalsIgnoreCase(type))
+ return "'"+value+" second'";
+ else if (SQL_TSI_HOUR.equalsIgnoreCase(type))
+ return "'"+value+" hour'";
+ else if (SQL_TSI_MINUTE.equalsIgnoreCase(type))
+ return "'"+value+" minute'";
+ else if (SQL_TSI_MONTH.equalsIgnoreCase(type))
+ return "'"+value+" month'";
+ else if (SQL_TSI_QUARTER.equalsIgnoreCase(type))
+ return "'"+ Integer.valueOf(value).intValue()*3+" month'";
+ else if (SQL_TSI_WEEK.equalsIgnoreCase(type))
+ return "'"+value+" week'";
+ else if (SQL_TSI_YEAR.equalsIgnoreCase(type))
+ return "'"+value+" year'";
+ else if (SQL_TSI_FRAC_SECOND.equalsIgnoreCase(type))
+ throw new PSQLException(GT.tr("Interval {0} not yet implemented","SQL_TSI_FRAC_SECOND"),
+ PSQLState.SYNTAX_ERROR);
+ else throw new PSQLException(GT.tr("Interval {0} not yet implemented",type),
+ PSQLState.SYNTAX_ERROR);
+ }
+
+
+ /** time stamp diff */
+ public static String sqltimestampdiff(List parsedArgs) throws SQLException{
+ if (parsedArgs.size()!=3){
+ throw new PSQLException(GT.tr("{0} function takes three and only three arguments.","timestampdiff"),
+ PSQLState.SYNTAX_ERROR);
+ }
+ String datePart = EscapedFunctions.constantToDatePart(parsedArgs.get (0).toString());
+ StringBuffer buf = new StringBuffer();
+ buf.append("extract( ").append(datePart)
+ .append(" from (").append(parsedArgs.get(2)).append("-").append(parsedArgs.get(1)).append("))");
+ return buf.toString();
+ }
+
+ private final static String constantToDatePart(String type)throws SQLException{
+ if (SQL_TSI_DAY.equalsIgnoreCase(type))
+ return "day";
+ else if (SQL_TSI_SECOND.equalsIgnoreCase(type))
+ return "second";
+ else if (SQL_TSI_HOUR.equalsIgnoreCase(type))
+ return "hour";
+ else if (SQL_TSI_MINUTE.equalsIgnoreCase(type))
+ return "minute";
+ else if (SQL_TSI_MONTH.equalsIgnoreCase(type))
+ return "month";
+ else if (SQL_TSI_QUARTER.equalsIgnoreCase(type))
+ return "quarter";
+ else if (SQL_TSI_WEEK.equalsIgnoreCase(type))
+ return "week";
+ else if (SQL_TSI_YEAR.equalsIgnoreCase(type))
+ return "year";
+ else if (SQL_TSI_FRAC_SECOND.equalsIgnoreCase(type))
+ throw new PSQLException(GT.tr("Interval {0} not yet implemented","SQL_TSI_FRAC_SECOND"),
+ PSQLState.SYNTAX_ERROR);
+ else throw new PSQLException(GT.tr("Interval {0} not yet implemented",type),
+ PSQLState.SYNTAX_ERROR);
+ }
+
/** database translation */
public static String sqldatabase(List parsedArgs) throws SQLException{
if (parsedArgs.size()!=0){
Index: test/jdbc2/StatementTest.java
================================================== =================
RCS file: /usr/local/cvsroot/pgjdbc/pgjdbc/org/postgresql/test/jdbc2/StatementTest.java,v
retrieving revision 1.19
diff -c -r1.19 StatementTest.java
*** test/jdbc2/StatementTest.java 1 Feb 2006 18:52:13 -0000 1.19
--- test/jdbc2/StatementTest.java 22 Mar 2006 14:47:45 -0000
***************
*** 297,302 ****
--- 297,334 ----
assertTrue(rs.next());
// ensure sunday =>1 and monday =>2
assertEquals(2,rs.getInt(5));
+ // second
+ rs = stmt.executeQuery("select {fn timestampdiff(SQL_TSI_SECOND,{fn now()},{fn timestampadd(SQL_TSI_SECOND,3,{fn now()})})} ");
+ assertTrue(rs.next());
+ assertEquals(3,rs.getInt(1));
+ // MINUTE
+ rs = stmt.executeQuery("select {fn timestampdiff(SQL_TSI_MINUTE,{fn now()},{fn timestampadd(SQL_TSI_MINUTE,3,{fn now()})})} ");
+ assertTrue(rs.next());
+ assertEquals(3,rs.getInt(1));
+ // HOUR
+ rs = stmt.executeQuery("select {fn timestampdiff(SQL_TSI_HOUR,{fn now()},{fn timestampadd(SQL_TSI_HOUR,3,{fn now()})})} ");
+ assertTrue(rs.next());
+ assertEquals(3,rs.getInt(1));
+ // day
+ rs = stmt.executeQuery("select {fn timestampdiff(SQL_TSI_DAY,{fn now()},{fn timestampadd(SQL_TSI_DAY,3,{fn now()})})} ");
+ assertTrue(rs.next());
+ assertEquals(3,rs.getInt(1));
+ // WEEK => extract week from interval is not supported by backend
+ //rs = stmt.executeQuery("select {fn timestampdiff(SQL_TSI_WEEK,{fn now()},{fn timestampadd(SQL_TSI_WEEK,3,{fn now()})})} ");
+ //assertTrue(rs.next());
+ //assertEquals(3,rs.getInt(1));
+ // MONTH => backend assume there are 0 month in an interval of 92 days...
+ //rs = stmt.executeQuery("select {fn timestampdiff(SQL_TSI_MONTH,{fn now()},{fn timestampadd(SQL_TSI_MONTH,3,{fn now()})})} ");
+ //assertTrue(rs.next());
+ //assertEquals(3,rs.getInt(1));
+ // QUARTER => backend assume there are 1 quater even in 270 days...
+ //rs = stmt.executeQuery("select {fn timestampdiff(SQL_TSI_QUARTER,{fn now()},{fn timestampadd(SQL_TSI_QUARTER,3,{fn now()})})} ");
+ //assertTrue(rs.next());
+ //assertEquals(3,rs.getInt(1));
+ // YEAR
+ //rs = stmt.executeQuery("select {fn timestampdiff(SQL_TSI_YEAR,{fn now()},{fn timestampadd(SQL_TSI_YEAR,3,{fn now()})})} ");
+ //assertTrue(rs.next());
+ //assertEquals(3,rs.getInt(1));
}

public void testSystemFunctions() throws SQLException


---------------------------(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

Dave Cramer a écrit :
> Xavier,
>
> Sorry I wasn't clear what I was trying to avoid is this
>
> you have 10 (didn't count) or so compares for SQL_TSI_*
>
> so what I am suggesting is that you compare for SQL_TSI_ and then
> compare for DAY, HOUR, etc in the sqltimestampdiff/sqltimestampadd
>
> You can determine quickly before creating the StringBuffer if you
> should even check for DAY,HOUR, etc.
>
> Plus the overhead of redundant checking of SQL_TSI_ is avoided.
>
> I realize these are nitpicky kinds of things, but I've recently become
> aware of certain java performance issues
>

The optimized version ...

Index: jdbc2/AbstractJdbc2DatabaseMetaData.java
================================================== =================
RCS file: /usr/local/cvsroot/pgjdbc/pgjdbc/org/postgresql/jdbc2/AbstractJdbc2DatabaseMetaData.java,v
retrieving revision 1.29
diff -c -r1.29 AbstractJdbc2DatabaseMetaData.java
*** jdbc2/AbstractJdbc2DatabaseMetaData.java 3 Feb 2006 21:10:15 -0000 1.29
--- jdbc2/AbstractJdbc2DatabaseMetaData.java 22 Mar 2006 16:16:54 -0000
***************
*** 477,483 ****
','+EscapedFunctions.MONTH+
','+EscapedFunctions.MONTHNAME+','+EscapedFunction s.NOW+
','+EscapedFunctions.QUARTER+','+EscapedFunctions. SECOND+
! ','+EscapedFunctions.WEEK+','+EscapedFunctions.YEA R;
}

/*
--- 477,484 ----
','+EscapedFunctions.MONTH+
','+EscapedFunctions.MONTHNAME+','+EscapedFunction s.NOW+
','+EscapedFunctions.QUARTER+','+EscapedFunctions. SECOND+
! ','+EscapedFunctions.WEEK+','+EscapedFunctions.YEA R+
! ','+EscapedFunctions.TIMESTAMPADD+','+EscapedFunct ions.TIMESTAMPDIFF;
}

/*
Index: jdbc2/EscapedFunctions.java
================================================== =================
RCS file: /usr/local/cvsroot/pgjdbc/pgjdbc/org/postgresql/jdbc2/EscapedFunctions.java,v
retrieving revision 1.6
diff -c -r1.6 EscapedFunctions.java
*** jdbc2/EscapedFunctions.java 4 Jun 2005 18:24:08 -0000 1.6
--- jdbc2/EscapedFunctions.java 22 Mar 2006 16:16:54 -0000
***************
*** 90,97 ****
public final static String SECOND="second";
public final static String WEEK="week";
public final static String YEAR="year";
! // TODO : timestampadd and timestampdiff

// system functions
public final static String DATABASE="database";
public final static String IFNULL="ifnull";
--- 90,113 ----
public final static String SECOND="second";
public final static String WEEK="week";
public final static String YEAR="year";
! // for timestampadd and timestampdiff the fractional part of second is not supported
! // by the backend
! public final static String TIMESTAMPADD="timestampadd";
! public final static String TIMESTAMPDIFF="timestampdiff";
!
! // constants for timestampadd and timestampdiff
! public final static String SQL_TSI_ROOT="SQL_TSI_";
! public final static String SQL_TSI_DAY="DAY";
! public final static String SQL_TSI_FRAC_SECOND="FRAC_SECOND";
! public final static String SQL_TSI_HOUR="HOUR";
! public final static String SQL_TSI_MINUTE="MINUTE";
! public final static String SQL_TSI_MONTH="MONTH";
! public final static String SQL_TSI_QUARTER="QUARTER";
! public final static String SQL_TSI_SECOND="SECOND";
! public final static String SQL_TSI_WEEK="WEEK";
! public final static String SQL_TSI_YEAR="YEAR";

+
// system functions
public final static String DATABASE="database";
public final static String IFNULL="ifnull";
***************
*** 478,483 ****
--- 494,582 ----
return "extract(year from "+parsedArgs.get(0)+")";
}

+ /** time stamp add */
+ public static String sqltimestampadd(List parsedArgs) throws SQLException{
+ if (parsedArgs.size()!=3){
+ throw new PSQLException(GT.tr("{0} function takes three and only three arguments.","timestampadd"),
+ PSQLState.SYNTAX_ERROR);
+ }
+ String interval = EscapedFunctions.constantToInterval(parsedArgs.get (0).toString(),parsedArgs.get(1).toString());
+ StringBuffer buf = new StringBuffer();
+ buf.append("(interval ").append(interval)
+ .append("+").append(parsedArgs.get(2)).append(")") ;
+ return buf.toString();
+ }
+
+ private final static String constantToInterval(String type,String value)throws SQLException{
+ if (!type.startsWith(SQL_TSI_ROOT))
+ throw new PSQLException(GT.tr("Interval {0} not yet implemented",type),
+ PSQLState.SYNTAX_ERROR);
+ String shortType = type.substring(SQL_TSI_ROOT.length());
+ if (SQL_TSI_DAY.equalsIgnoreCase(shortType))
+ return "'"+value+" day'";
+ else if (SQL_TSI_SECOND.equalsIgnoreCase(shortType))
+ return "'"+value+" second'";
+ else if (SQL_TSI_HOUR.equalsIgnoreCase(shortType))
+ return "'"+value+" hour'";
+ else if (SQL_TSI_MINUTE.equalsIgnoreCase(shortType))
+ return "'"+value+" minute'";
+ else if (SQL_TSI_MONTH.equalsIgnoreCase(shortType))
+ return "'"+value+" month'";
+ else if (SQL_TSI_QUARTER.equalsIgnoreCase(shortType))
+ return "'"+ Integer.valueOf(value).intValue()*3+" month'";
+ else if (SQL_TSI_WEEK.equalsIgnoreCase(shortType))
+ return "'"+value+" week'";
+ else if (SQL_TSI_YEAR.equalsIgnoreCase(shortType))
+ return "'"+value+" year'";
+ else if (SQL_TSI_FRAC_SECOND.equalsIgnoreCase(shortType))
+ throw new PSQLException(GT.tr("Interval {0} not yet implemented","SQL_TSI_FRAC_SECOND"),
+ PSQLState.SYNTAX_ERROR);
+ else throw new PSQLException(GT.tr("Interval {0} not yet implemented",type),
+ PSQLState.SYNTAX_ERROR);
+ }
+
+
+ /** time stamp diff */
+ public static String sqltimestampdiff(List parsedArgs) throws SQLException{
+ if (parsedArgs.size()!=3){
+ throw new PSQLException(GT.tr("{0} function takes three and only three arguments.","timestampdiff"),
+ PSQLState.SYNTAX_ERROR);
+ }
+ String datePart = EscapedFunctions.constantToDatePart(parsedArgs.get (0).toString());
+ StringBuffer buf = new StringBuffer();
+ buf.append("extract( ").append(datePart)
+ .append(" from (").append(parsedArgs.get(2)).append("-").append(parsedArgs.get(1)).append("))");
+ return buf.toString();
+ }
+
+ private final static String constantToDatePart(String type)throws SQLException{
+ if (!type.startsWith(SQL_TSI_ROOT))
+ throw new PSQLException(GT.tr("Interval {0} not yet implemented",type),
+ PSQLState.SYNTAX_ERROR);
+ String shortType = type.substring(SQL_TSI_ROOT.length());
+ if (SQL_TSI_DAY.equalsIgnoreCase(shortType))
+ return "day";
+ else if (SQL_TSI_SECOND.equalsIgnoreCase(shortType))
+ return "second";
+ else if (SQL_TSI_HOUR.equalsIgnoreCase(shortType))
+ return "hour";
+ else if (SQL_TSI_MINUTE.equalsIgnoreCase(shortType))
+ return "minute";
+ else if (SQL_TSI_MONTH.equalsIgnoreCase(shortType))
+ return "month";
+ else if (SQL_TSI_QUARTER.equalsIgnoreCase(shortType))
+ return "quarter";
+ else if (SQL_TSI_WEEK.equalsIgnoreCase(shortType))
+ return "week";
+ else if (SQL_TSI_YEAR.equalsIgnoreCase(shortType))
+ return "year";
+ else if (SQL_TSI_FRAC_SECOND.equalsIgnoreCase(shortType))
+ throw new PSQLException(GT.tr("Interval {0} not yet implemented","SQL_TSI_FRAC_SECOND"),
+ PSQLState.SYNTAX_ERROR);
+ else throw new PSQLException(GT.tr("Interval {0} not yet implemented",type),
+ PSQLState.SYNTAX_ERROR);
+ }
+
/** database translation */
public static String sqldatabase(List parsedArgs) throws SQLException{
if (parsedArgs.size()!=0){
Index: test/jdbc2/StatementTest.java
================================================== =================
RCS file: /usr/local/cvsroot/pgjdbc/pgjdbc/org/postgresql/test/jdbc2/StatementTest.java,v
retrieving revision 1.19
diff -c -r1.19 StatementTest.java
*** test/jdbc2/StatementTest.java 1 Feb 2006 18:52:13 -0000 1.19
--- test/jdbc2/StatementTest.java 22 Mar 2006 16:16:54 -0000
***************
*** 297,302 ****
--- 297,334 ----
assertTrue(rs.next());
// ensure sunday =>1 and monday =>2
assertEquals(2,rs.getInt(5));
+ // second
+ rs = stmt.executeQuery("select {fn timestampdiff(SQL_TSI_SECOND,{fn now()},{fn timestampadd(SQL_TSI_SECOND,3,{fn now()})})} ");
+ assertTrue(rs.next());
+ assertEquals(3,rs.getInt(1));
+ // MINUTE
+ rs = stmt.executeQuery("select {fn timestampdiff(SQL_TSI_MINUTE,{fn now()},{fn timestampadd(SQL_TSI_MINUTE,3,{fn now()})})} ");
+ assertTrue(rs.next());
+ assertEquals(3,rs.getInt(1));
+ // HOUR
+ rs = stmt.executeQuery("select {fn timestampdiff(SQL_TSI_HOUR,{fn now()},{fn timestampadd(SQL_TSI_HOUR,3,{fn now()})})} ");
+ assertTrue(rs.next());
+ assertEquals(3,rs.getInt(1));
+ // day
+ rs = stmt.executeQuery("select {fn timestampdiff(SQL_TSI_DAY,{fn now()},{fn timestampadd(SQL_TSI_DAY,3,{fn now()})})} ");
+ assertTrue(rs.next());
+ assertEquals(3,rs.getInt(1));
+ // WEEK => extract week from interval is not supported by backend
+ //rs = stmt.executeQuery("select {fn timestampdiff(SQL_TSI_WEEK,{fn now()},{fn timestampadd(SQL_TSI_WEEK,3,{fn now()})})} ");
+ //assertTrue(rs.next());
+ //assertEquals(3,rs.getInt(1));
+ // MONTH => backend assume there are 0 month in an interval of 92 days...
+ //rs = stmt.executeQuery("select {fn timestampdiff(SQL_TSI_MONTH,{fn now()},{fn timestampadd(SQL_TSI_MONTH,3,{fn now()})})} ");
+ //assertTrue(rs.next());
+ //assertEquals(3,rs.getInt(1));
+ // QUARTER => backend assume there are 1 quater even in 270 days...
+ //rs = stmt.executeQuery("select {fn timestampdiff(SQL_TSI_QUARTER,{fn now()},{fn timestampadd(SQL_TSI_QUARTER,3,{fn now()})})} ");
+ //assertTrue(rs.next());
+ //assertEquals(3,rs.getInt(1));
+ // YEAR
+ //rs = stmt.executeQuery("select {fn timestampdiff(SQL_TSI_YEAR,{fn now()},{fn timestampadd(SQL_TSI_YEAR,3,{fn now()})})} ");
+ //assertTrue(rs.next());
+ //assertEquals(3,rs.getInt(1));
}

public void testSystemFunctions() throws SQLException


---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

Xavier

The semantics of extract is that it will give the month/hour/minute
of the interval, not the number of months/hours/days of the interval

Dave
On 22-Mar-06, at 11:25 AM, Xavier Poinsard wrote:

> Dave Cramer a écrit :
>>> You are right, but I would not expect 0 with :
>>>
>>> select extract(month from
>>> (current_date+ interval '3 month'-current_date));
>>>
>>> date_part
>>> -----------
>>> 0
>>> (1 row)
>>
>>
>> This is still an interval basically you have
>>
>> date + interval - date which will be an interval. date - date is an
>> interval
>
> I think the interval implementation is bit deficient, since it only
> keeps one unit. Instead of keeping only the difference in days, it
> could
> have keep the information in month too.
>
> test=> select (current_date+interval '3 month'-current_date);
> ?column?
> ----------
> 92 days
> (1 row)
>
> test=> select extract(month from (interval '3 month'));
> date_part
> -----------
> 3
> (1 row)
>
> select extract(month from (interval '1 year'));
> date_part
> -----------
> 0
> (1 row)
>


---------------------------(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

Here are the updated patchs.

Dave Cramer a écrit :
> Xavier,
>
> So at this point do you want to remove some of the less correct escapes ?
>

Index: jdbc2/AbstractJdbc2DatabaseMetaData.java
================================================== =================
RCS file: /usr/local/cvsroot/pgjdbc/pgjdbc/org/postgresql/jdbc2/AbstractJdbc2DatabaseMetaData.java,v
retrieving revision 1.29
diff -c -r1.29 AbstractJdbc2DatabaseMetaData.java
*** jdbc2/AbstractJdbc2DatabaseMetaData.java 3 Feb 2006 21:10:15 -0000 1.29
--- jdbc2/AbstractJdbc2DatabaseMetaData.java 24 Mar 2006 12:00:11 -0000
***************
*** 477,483 ****
','+EscapedFunctions.MONTH+
','+EscapedFunctions.MONTHNAME+','+EscapedFunction s.NOW+
','+EscapedFunctions.QUARTER+','+EscapedFunctions. SECOND+
! ','+EscapedFunctions.WEEK+','+EscapedFunctions.YEA R;
}

/*
--- 477,484 ----
','+EscapedFunctions.MONTH+
','+EscapedFunctions.MONTHNAME+','+EscapedFunction s.NOW+
','+EscapedFunctions.QUARTER+','+EscapedFunctions. SECOND+
! ','+EscapedFunctions.WEEK+','+EscapedFunctions.YEA R+
! ','+EscapedFunctions.TIMESTAMPADD; //+','+EscapedFunctions.TIMESTAMPDIFF;
}

/*
Index: jdbc2/EscapedFunctions.java
================================================== =================
RCS file: /usr/local/cvsroot/pgjdbc/pgjdbc/org/postgresql/jdbc2/EscapedFunctions.java,v
retrieving revision 1.6
diff -c -r1.6 EscapedFunctions.java
*** jdbc2/EscapedFunctions.java 4 Jun 2005 18:24:08 -0000 1.6
--- jdbc2/EscapedFunctions.java 24 Mar 2006 12:00:11 -0000
***************
*** 90,97 ****
public final static String SECOND="second";
public final static String WEEK="week";
public final static String YEAR="year";
! // TODO : timestampadd and timestampdiff

// system functions
public final static String DATABASE="database";
public final static String IFNULL="ifnull";
--- 90,114 ----
public final static String SECOND="second";
public final static String WEEK="week";
public final static String YEAR="year";
! // for timestampadd and timestampdiff the fractional part of second is not supported
! // by the backend
! // timestampdiff is very partially supported
! public final static String TIMESTAMPADD="timestampadd";
! public final static String TIMESTAMPDIFF="timestampdiff";
!
! // constants for timestampadd and timestampdiff
! public final static String SQL_TSI_ROOT="SQL_TSI_";
! public final static String SQL_TSI_DAY="DAY";
! public final static String SQL_TSI_FRAC_SECOND="FRAC_SECOND";
! public final static String SQL_TSI_HOUR="HOUR";
! public final static String SQL_TSI_MINUTE="MINUTE";
! public final static String SQL_TSI_MONTH="MONTH";
! public final static String SQL_TSI_QUARTER="QUARTER";
! public final static String SQL_TSI_SECOND="SECOND";
! public final static String SQL_TSI_WEEK="WEEK";
! public final static String SQL_TSI_YEAR="YEAR";

+
// system functions
public final static String DATABASE="database";
public final static String IFNULL="ifnull";
***************
*** 478,483 ****
--- 495,583 ----
return "extract(year from "+parsedArgs.get(0)+")";
}

+ /** time stamp add */
+ public static String sqltimestampadd(List parsedArgs) throws SQLException{
+ if (parsedArgs.size()!=3){
+ throw new PSQLException(GT.tr("{0} function takes three and only three arguments.","timestampadd"),
+ PSQLState.SYNTAX_ERROR);
+ }
+ String interval = EscapedFunctions.constantToInterval(parsedArgs.get (0).toString(),parsedArgs.get(1).toString());
+ StringBuffer buf = new StringBuffer();
+ buf.append("(interval ").append(interval)
+ .append("+").append(parsedArgs.get(2)).append(")") ;
+ return buf.toString();
+ }
+
+ private final static String constantToInterval(String type,String value)throws SQLException{
+ if (!type.startsWith(SQL_TSI_ROOT))
+ throw new PSQLException(GT.tr("Interval {0} not yet implemented",type),
+ PSQLState.SYNTAX_ERROR);
+ String shortType = type.substring(SQL_TSI_ROOT.length());
+ if (SQL_TSI_DAY.equalsIgnoreCase(shortType))
+ return "'"+value+" day'";
+ else if (SQL_TSI_SECOND.equalsIgnoreCase(shortType))
+ return "'"+value+" second'";
+ else if (SQL_TSI_HOUR.equalsIgnoreCase(shortType))
+ return "'"+value+" hour'";
+ else if (SQL_TSI_MINUTE.equalsIgnoreCase(shortType))
+ return "'"+value+" minute'";
+ else if (SQL_TSI_MONTH.equalsIgnoreCase(shortType))
+ return "'"+value+" month'";
+ else if (SQL_TSI_QUARTER.equalsIgnoreCase(shortType))
+ return "'"+ Integer.valueOf(value).intValue()*3+" month'";
+ else if (SQL_TSI_WEEK.equalsIgnoreCase(shortType))
+ return "'"+value+" week'";
+ else if (SQL_TSI_YEAR.equalsIgnoreCase(shortType))
+ return "'"+value+" year'";
+ else if (SQL_TSI_FRAC_SECOND.equalsIgnoreCase(shortType))
+ throw new PSQLException(GT.tr("Interval {0} not yet implemented","SQL_TSI_FRAC_SECOND"),
+ PSQLState.SYNTAX_ERROR);
+ else throw new PSQLException(GT.tr("Interval {0} not yet implemented",type),
+ PSQLState.SYNTAX_ERROR);
+ }
+
+
+ /** time stamp diff */
+ public static String sqltimestampdiff(List parsedArgs) throws SQLException{
+ if (parsedArgs.size()!=3){
+ throw new PSQLException(GT.tr("{0} function takes three and only three arguments.","timestampdiff"),
+ PSQLState.SYNTAX_ERROR);
+ }
+ String datePart = EscapedFunctions.constantToDatePart(parsedArgs.get (0).toString());
+ StringBuffer buf = new StringBuffer();
+ buf.append("extract( ").append(datePart)
+ .append(" from (").append(parsedArgs.get(2)).append("-").append(parsedArgs.get(1)).append("))");
+ return buf.toString();
+ }
+
+ private final static String constantToDatePart(String type)throws SQLException{
+ if (!type.startsWith(SQL_TSI_ROOT))
+ throw new PSQLException(GT.tr("Interval {0} not yet implemented",type),
+ PSQLState.SYNTAX_ERROR);
+ String shortType = type.substring(SQL_TSI_ROOT.length());
+ if (SQL_TSI_DAY.equalsIgnoreCase(shortType))
+ return "day";
+ else if (SQL_TSI_SECOND.equalsIgnoreCase(shortType))
+ return "second";
+ else if (SQL_TSI_HOUR.equalsIgnoreCase(shortType))
+ return "hour";
+ else if (SQL_TSI_MINUTE.equalsIgnoreCase(shortType))
+ return "minute";
+ /*else if (SQL_TSI_MONTH.equalsIgnoreCase(shortType))
+ return "month";
+ else if (SQL_TSI_QUARTER.equalsIgnoreCase(shortType))
+ return "quarter";
+ else if (SQL_TSI_WEEK.equalsIgnoreCase(shortType))
+ return "week";
+ else if (SQL_TSI_YEAR.equalsIgnoreCase(shortType))
+ return "year";*/
+ else if (SQL_TSI_FRAC_SECOND.equalsIgnoreCase(shortType))
+ throw new PSQLException(GT.tr("Interval {0} not yet implemented","SQL_TSI_FRAC_SECOND"),
+ PSQLState.SYNTAX_ERROR);
+ else throw new PSQLException(GT.tr("Interval {0} not yet implemented",type),
+ PSQLState.SYNTAX_ERROR);
+ }
+
/** database translation */
public static String sqldatabase(List parsedArgs) throws SQLException{
if (parsedArgs.size()!=0){
Index: test/jdbc2/StatementTest.java
================================================== =================
RCS file: /usr/local/cvsroot/pgjdbc/pgjdbc/org/postgresql/test/jdbc2/StatementTest.java,v
retrieving revision 1.19
diff -c -r1.19 StatementTest.java
*** test/jdbc2/StatementTest.java 1 Feb 2006 18:52:13 -0000 1.19
--- test/jdbc2/StatementTest.java 24 Mar 2006 12:00:11 -0000
***************
*** 297,302 ****
--- 297,334 ----
assertTrue(rs.next());
// ensure sunday =>1 and monday =>2
assertEquals(2,rs.getInt(5));
+ // second
+ rs = stmt.executeQuery("select {fn timestampdiff(SQL_TSI_SECOND,{fn now()},{fn timestampadd(SQL_TSI_SECOND,3,{fn now()})})} ");
+ assertTrue(rs.next());
+ assertEquals(3,rs.getInt(1));
+ // MINUTE
+ rs = stmt.executeQuery("select {fn timestampdiff(SQL_TSI_MINUTE,{fn now()},{fn timestampadd(SQL_TSI_MINUTE,3,{fn now()})})} ");
+ assertTrue(rs.next());
+ assertEquals(3,rs.getInt(1));
+ // HOUR
+ rs = stmt.executeQuery("select {fn timestampdiff(SQL_TSI_HOUR,{fn now()},{fn timestampadd(SQL_TSI_HOUR,3,{fn now()})})} ");
+ assertTrue(rs.next());
+ assertEquals(3,rs.getInt(1));
+ // day
+ rs = stmt.executeQuery("select {fn timestampdiff(SQL_TSI_DAY,{fn now()},{fn timestampadd(SQL_TSI_DAY,-3,{fn now()})})} ");
+ assertTrue(rs.next());
+ assertEquals(-3,rs.getInt(1));
+ // WEEK => extract week from interval is not supported by backend
+ //rs = stmt.executeQuery("select {fn timestampdiff(SQL_TSI_WEEK,{fn now()},{fn timestampadd(SQL_TSI_WEEK,3,{fn now()})})} ");
+ //assertTrue(rs.next());
+ //assertEquals(3,rs.getInt(1));
+ // MONTH => backend assume there are 0 month in an interval of 92 days...
+ //rs = stmt.executeQuery("select {fn timestampdiff(SQL_TSI_MONTH,{fn now()},{fn timestampadd(SQL_TSI_MONTH,3,{fn now()})})} ");
+ //assertTrue(rs.next());
+ //assertEquals(3,rs.getInt(1));
+ // QUARTER => backend assume there are 1 quater even in 270 days...
+ //rs = stmt.executeQuery("select {fn timestampdiff(SQL_TSI_QUARTER,{fn now()},{fn timestampadd(SQL_TSI_QUARTER,3,{fn now()})})} ");
+ //assertTrue(rs.next());
+ //assertEquals(3,rs.getInt(1));
+ // YEAR
+ //rs = stmt.executeQuery("select {fn timestampdiff(SQL_TSI_YEAR,{fn now()},{fn timestampadd(SQL_TSI_YEAR,3,{fn now()})})} ");
+ //assertTrue(rs.next());
+ //assertEquals(3,rs.getInt(1));
}

public void testSystemFunctions() throws SQLException

Index: pgjdbc.xml
================================================== =================
RCS file: /usr/local/cvsroot/pgjdbc/pgjdbc/doc/pgjdbc.xml,v
retrieving revision 1.27
diff -c -r1.27 pgjdbc.xml
*** pgjdbc.xml 24 Nov 2005 06:36:03 -0000 1.27
--- pgjdbc.xml 24 Mar 2006 12:00:39 -0000
***************
*** 2040,2045 ****
--- 2040,2057 ----
<entry>extract(year from arg1)</entry>
<entry></entry>
</row>
+ <row>
+ <entry>timestampadd(argIntervalType,argCount,argTi meStamp)</entry>
+ <entry>yes</entry>
+ <entry>('(interval according to argIntervalType and argCount)'+argTimeStamp)</entry>
+ <entry>an argIntervalType value of <classname>SQL_TSI_FRAC_SECOND</classname> is not implemented since backend does not support it</entry>
+ </row>
+ <row>
+ <entry>timestampdiff(argIntervalType,argTimeStamp1 ,argTimeStamp2)</entry>
+ <entry>not</entry>
+ <entry>extract((interval according to argIntervalType) from argTimeStamp2-argTimeStamp1 )</entry>
+ <entry>only an argIntervalType value of <classname>SQL_TSI_FRAC_SECOND</classname>,<classname>SQL_TSI_FRAC_MINUTE</classname>,<classname>SQL_TSI_FRAC_HOUR</classname> or <classname>SQL_TSI_FRAC_DAY</classname> is supported </entry>
+ </row>
</tbody>
</tgroup>
</table>


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