Mailing-List: contact derby-dev-help@db.apache.org; run by ezmlm
Precedence: bulk
Reply-To: "Derby Development" <derby-dev@db.apache.org>
Received-SPF: neutral (hermes.apache.org: local policy)
Message-ID: <4288DAE5.9090008@debrunners.com>
Date: Mon, 16 May 2005 10:39:49 -0700
From: Daniel John Debrunner <djd@debrunners.com>
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US;
 rv:1.7.3) Gecko/20040910
MIME-Version: 1.0
To: Derby Development <derby-dev@db.apache.org>
Subject: Re: [PATCH] Timestamp Arithmetic
References: <4284D2C1.3090902@sbcglobal.net>
In-Reply-To: <4284D2C1.3090902@sbcglobal.net>
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit


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
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.]

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.

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.

Dan.