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
