db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Bernt M. Johnsen" <Bernt.John...@Sun.COM>
Subject Re: Question - Second function
Date Tue, 03 Oct 2006 08:06:45 GMT
Laura Stewart wrote:
> On 10/2/06, Daniel J Debrunner <djd@apache.org> wrote:
>>
>>
>> --- Laura Stewart <scotsmatrix@gmail.com> 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

Mime
View raw message