db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Don Smith (JIRA)" <j...@apache.org>
Subject [jira] Commented: (DERBY-2386) timestampdiff function fails when using SQL_TSI_FRAC_SECOND for datepart parameter, except for very small intervals
Date Tue, 06 Mar 2007 15:24:24 GMT

    [ https://issues.apache.org/jira/browse/DERBY-2386?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#action_12478442
] 

Don Smith commented on DERBY-2386:
----------------------------------

It seems counter-intuitive to require foreknowledge of the interval magnitude in order to
select the correct datepart parameter. If seconds returned the fractional portion, then it
would be sufficient to always use seconds, and multiply within application code as needed.
But the nature of data-driven programs is that we don't know whether the interval will be
less than a second, less than a minute, or even less than a day.

By limiting the function return value to integer, the usefulness of this function is severly
limited. Many applications require fractional-second accuracy to time certain events, events
which could possibly take > 1 second.

Calculating an interval in Java uses milliseconds, i.e. date1.getTimeInMillis() - date2.getTimeInMillis().
It seems like a Java-based dbms should be able to mimic this Java concept.

I urge you to expand the return type to BIGINT (Java long).

> timestampdiff function fails when using SQL_TSI_FRAC_SECOND for datepart parameter, except
for very small intervals
> -------------------------------------------------------------------------------------------------------------------
>
>                 Key: DERBY-2386
>                 URL: https://issues.apache.org/jira/browse/DERBY-2386
>             Project: Derby
>          Issue Type: Bug
>          Components: SQL
>    Affects Versions: 10.2.2.0
>         Environment: SUSE Linux Enterprise Desktop 10
>            Reporter: Don Smith
>         Assigned To: Mayuresh Nirhali
>
> Using the timestampdiff function produces and integer overflow except for very small
intervals. Error message is:
> [Error Code: -1, SQL State: 22003]  The resulting value is outside the range for the
data type INTEGER.
> I inserted the following row into my test table:
> insert into datetest (ID, 
> startdate, 
> enddate) values (
> 5, 
> '2006-11-20 04:20:00.0', 
> '2006-11-20 04:20:00.2');
> This test row works:
> select {fn timestampdiff(SQL_TSI_FRAC_SECOND, startdate, enddate)} as diff from datetest
where id = 5
> DIFF
> 200000000
> The value also looks too large, which could be exacerbating the problem.

-- 
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.


Mime
View raw message