Provide ability to get exact results when use timestamp arithmetic with fractional part of second (DATEADD(), DATEDIFF()).

[pavel-zotov]

Passing fractional values of 1st argument into DATEADD() function can show 'weird' results:

SQL> select dateadd(-55.499 second to timestamp '01.01.2000 00:00:55.499') from rdb$database;
2000-01-01 00:00:00.4990

SQL> select dateadd(-55.501 second to timestamp '01.01.2000 00:00:55.501') from rdb$database;
1999-12-31 23:59:59.5010

No such problem if we use integer values in 1st argument and reduce 'time frame' (from second to millisecond):

SQL> select dateadd(-55499 millisecond to timestamp '01.01.2000 00:00:55.499') from rdb$database;
2000-01-01 00:00:00.0000

SQL> select dateadd(-55501 millisecond to timestamp '01.01.2000 00:00:55.501') from rdb$database;
2000-01-01 00:00:00.0000

So, 1st argument for DATEADD() is rounded to integer value (-55.499 --> -55; -55.501 --> -56) and only after this it will be added to timestamp. It will be useful to have feature that allow to 'turn off' such rounding (may be as parameter in firebird.conf).

This feature must also provide ability to get exact result for such:

SQL> select datediff(second from  timestamp '01.01.2000 00:00:55.499' to  timestamp '01.01.2000 00:00:55.501' ) from rdb$database;

(currently result of this is 0 because of rounding; one need to to use datediff(millisecond ....) and divide it by 1000.00 if we want to get exact value).

PS. There are two tickets about fractional part for timeframe, but they relate to millisecond:

• core_4457 // DATEADD should support fractional value for MILLISECOND
• core_6987 // DATEDIFF does not support fractional value for MILLISECOND I could not find ticket related to fractional part of SECOND frame.

[mrotteveel]

IIRC, the standard mandates that second is an integer (I will verify when I have access to the standard), so the current behaviour is IMHO correct.

[pavel-zotov]

SQL:2011 states that SECOND can have fractional part (both in DATETIME and INTERVAL datatypes).

[dyemanov]

I'd expect DATEADD/DATEDIFF being in sync with EXTRACT re. SECOND handling and we can see that fractions are returned by EXTRACT:

SQL> select extract(second from current_timestamp) from rdb$database;

     EXTRACT 
============ 
     41.1450 

AFAIU, this is because MILLISECOND is our non-standard extension. Thus DATEADD/DATEDIFF should also support fractional seconds.

[asfernandes]

But look, it's not about SECOND only:

SQL> select dateadd(1.5 day to timestamp '01.01.2000 00:00:55.499') from rdb$database;

                  DATEADD 
========================= 
2000-01-03 00:00:55.4990  

It's consistent with other field types.

On the other hand, as Dmitry said, it's inconsistent with EXTRACT.

[dyemanov]

@asfernandes, it's kinda expected for DAY/MONTH/YEAR, because (a) they're defined as integers in the SQL spec and (b) EXTRACT returns them as integers. So rounding is understandable. However, SECOND may be expected to act as decimal (by SQL spec and by EXTRACT), which is not the case for DATEADD/DATEDIFF.