db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Jack Klebanoff <klebanoff-de...@sbcglobal.net>
Subject Re: [PATCH] Timestamp Arithmetic
Date Tue, 17 May 2005 15:53:04 GMT
Daniel John Debrunner wrote:

>Jack wrote ...
>
>A few items in your patch & description gave me pause for thought ...
>
>  
>
>>If a date is used in ts1 or ts2 it is converted to a timestamp by using time 00:00:00.
>>If a time is used in ts1 or ts2 it is converted to a timestamp by using the current
date. 
>>    
>>
>
>a) the lack of symmetry in these statements, current date but not
>current time
>  
>
My copy of the JDBC 3.0 spec does not say what TIMESTAMPADD and 
TIMESTAMPDIFF are supposed to do when the input is a date or time. 
However the ODBC spec on Microsoft's web site specifies the above 
behavior. My understanding is that JDBC follows ODBC here.

>b) what did you mean by current time, as there is "SQL current
>timestamp" and "real time now"? I see you implemented as real time. [SQL
>has the CURRENT_TIMESTAMP fixed for the lifetime of a statement]
>c) current date in which timezone?
>
>[I see that Derby uses this rule for casting from a TIME to a TIMESTAMP,
>and the manuals state CURRENT_DATE, which would be fixed for the
>lifetime of the statement.]
>  
>
Good point. I will look into changing the time to timestamp conversion 
to use the same logic as CURRENT_DATE.

>a) means that TIMESTAMPADD() can return different values for a given
>TIME argument value at different times, e.g. TIME + 1 DAY will depend on
>which day the function is executed.
>
>That combined with b) means that within a single query TIMESTAMPADD()
>can return different values for a given TIME argument value, i.e. if a
>query's execution spans midnight.
>
>That behaviour for a SQL function just seems wrong.
>
>Then the naming of the new methods in DateTimeDataValue seemed wrong,
>I'm a great believer in ensuring such items and method names correctly
>reflect their purpose. This makes the code more readable.
>So we have
>
>  
>
>>+    DateTimeDataValue timestampAdd( int intervalType, NumberDataValue intervalCount,
DateTimeDataValue resultHolder)
>>+        throws StandardException;
>>    
>>
>
>If I have a expression equivalent to TIME + 1 MIN, then I'm not adding a
>timestamp, I'm adding one minute to the current value of the
>DateTimeDataValue object. This is an interface method, declaration of
>behaviour, not implementation.
>
>Similar for
>
>  
>
>>+    NumberDataValue timestampDiff( int intervalType, DateTimeDataValue time1, NumberDataValue
resultHolder)
>>+        throws StandardException;
>>    
>>
>
>If I'm diffing two TIME values and returning interval in minutes then no
>timestamp is involved.
>
>It seems a more correct reflection of their names would be intervalAdd
>and intervalDiff.
>  
>
The methods implement the TIMESTAMPADD and TIMESTAMPDIFF functions, so 
their names seem appropriate to me.

>So all this thinking got me to the key point is I think there are
>combinations of interval and SQL types that should not be allowed.
>
> - Since SQL TIME is not associated with a date then I believe that
>adding or diffing any date related intervals should not be allowed.
>
> - Since SQL DATE is not associated with a time then I believe that
>adding or diffing any time related intervals should not be allowed.
>
> - And diffing TIME against a DATE etc. should not be allowed.
>[this matches the existing documentation that says different date time
>types cannot be mixed in an expression]
>
>So with JDBC escape functions equivalent to these expressions
>
>TIME + 1 DAY // not allowed
>
>DATE + 1 MIN // not allowed
>
>DATE - TIME // not allowed
>
>
>Of course this leads to either (and similar for DATE)
>
>{fn TIMESTAMPADD( interval, count, <TIME expression>)}
>
>returns a TIME value, or is not supported altogether.
>  
>
I think that this is a reasonable argument, but I think that we should 
follow the JDBC/ODBC standard, warts and all.

>Dan.
>
>
>
>  
>
Jack


Mime
View raw message