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