Laura Stewart wrote:
> On 10/2/06, Daniel J Debrunner <[EMAIL PROTECTED]> wrote:
>>
>>
>> --- Laura Stewart <[EMAIL PROTECTED]> wrote:
>>
>> > Hi -
>> >
>> > From the 10.2 Doc Review, I have this question about
>> > the SECOND function
>> >
>> http://db.apache.org/derby/docs/10.2/ref/rrefsecondfunc.html
I don't think the doc's are correct here. If the argument to seconds is a
timestamp, the result is a double. See example below:
ij> create table ts (t timestamp);
0 rows inserted/updated/deleted
ij> insert into ts values (current_timestamp);
1 row inserted/updated/deleted
ij> select t, second(t), second(cast(t as time)),
second('1999-09-09 09:09:09.999') from ts;
T |2 |3 |4
-------------------------------------------------------------------------
2006-10-03 09:49:58.306 |58.306 |58 |9
1 row selected
ij>
But as you see, there's an anomality here, since the timestamp string (col 4)
does not return 9.999 as I would have expected.
Retuning a double here is also in line with the SQL standard:
5) If <extract expression> is specified, then
Case:
a) If <primary datetime field> does not specify SECOND, then the
declared type of the result is an implementation-defined exact
numeric type with scale 0 (zero).
b) Otherwise, the declared type of the result is an
implementation-defined exact numeric type with scale not less than
the specified or implied <time fractional seconds precision> or
<interval fractional seconds precision>, as appropriate, of the
SECOND <primary datetime field> of the <extract source>.
(BTW: The datetime scalar functions are e.g. {fn SECOND(t)} in JDBC escape
syntx, but should have been EXTRACT(SECOND FROM t) in SQL had Derby been fully
stabdards compliant)
>> >
>> > The text:
>> > 'If the argument is a time duration or timestamp
>> > duration: The result
>> > is the seconds part of the value, which is an
>> > integer between -99 and
>> > 99. A nonzero result has the same sign as the
>> > argument."
>> >
>> > 1) I don't understand how you can get an integer
>> > between -99 and 99.
>> > Could someone provide an example that we could
>> > include with the docs?
>> >
>>
>> I think this is covered by DERBY-908
>> http://issues.apache.org/jira/browse/DERBY-908
>>
>> Dan.
>>
>
> Thanks, I'll fix them in this issue.
>
--
Bernt Marius Johnsen, Database Technology Group,
Staff Engineer, Technical Lead Derby/Java DB
Sun Microsystems, Trondheim, Norway
