DATEDIFF does not support fractional value for MILLISECOND

[dmitry-lipetsk]

Hello,

Firebird 3.0.8.33429 (SS, Windows).

DATEADD supports "fractional value for MILLISECOND"

select CAST(DATEADD(MILLISECOND, 0.1, timestamp '0001-01-01') AS VARCHAR(32)) from RDB$DATABASE
returns 0001-01-01 00:00:00.0001

And EXTRACT supports "fractional value for MILLISECOND"

select CAST(EXTRACT(MILLISECOND from timestamp '0001-01-01 00:00:00.0001') AS VARCHAR(32)) from RDB$DATABASE
returns 0.1

But DATEDIFF does not support "fractional value for MILLISECOND"

select CAST(DATEDIFF(MILLISECOND FROM timestamp '0001-01-01' TO timestamp '0001-01-01 00:00:00.0001') AS VARCHAR(32)) from RDB$DATABASE

returns 0 (BIGINT value)

---

This limitation creates a some problem with correct translations of System.TimeSpan functional (C#, EFCore).

It will be nice see improving of this in FB3 also.

Thanks.

[dmitry-lipetsk]

Hello @asfernandes,

Updated FB3 works incorrectly?

select CAST
        (DATEADD
         (MILLISECOND,
          DATEDIFF(MILLISECOND FROM timestamp '0001-01-01 00:00:00.0001' TO timestamp '9999-12-31 23:59:59.9999'),
          timestamp '0001-01-01 00:00:00.0001')
         AS VARCHAR(32)) from rdb$database

returns 1000-11-26 19:11:59.9999

expected - 9999-12-31 23:59:59.9999

select CAST
        (DATEADD
         (MILLISECOND,
          DATEDIFF(MILLISECOND FROM timestamp '9999-12-31 23:59:59.9999' TO timestamp '0001-01-01 00:00:00.0001'),
          timestamp '9999-12-31 23:59:59.9999')
         AS VARCHAR(32)) from rdb$database

returns 9000-02-05 04:48:00.0001

expected 0001-01-01 00:00:00.0001

[mrotteveel]

I don't think this is a change that should go into Firebird 3 and 4, but only in Firebird 5, as it changes the return type of a function and thus is a change that could break things.

[asfernandes]

Updated FB3 works incorrectly?

Should be fixed now, thanks.

[asfernandes]

I don't think this is a change that should go into Firebird 3 and 4, but only in Firebird 5, as it changes the return type of a function and thus is a change that could break things.

If I'm not wrong, v4.0.1 will have change in types of some numeric operations already.

It's just the scale which was been changed. I believe any well written application could deal with it.

Otherwise we should stop doing point-releases as no bug could be fixed.

[mrotteveel]

@asfernandes This is not a bug fix, this is a change to an existing feature. Such changes should, unless there are clear and important circumstances, not be done in point releases, in my opinion.

[dyemanov]

While formally Mark is correct, every rule may have exceptions. So far I don't see any big harm from this change.

[todaysoftware]

After upgrading from 3.0.7 to 3.0.9 it broke some stored procedures for us that were doing BIN_SHL(DATEDIFF(...

Arguments for BIN_SHL must be integral types or NUMERIC/DECIMAL without scale

Updating the documentation would be helpful as that was the first place I checked and it tells me it is going to return a BIGINT which it is clearly is not anymore...

https://firebirdsql.org/file/documentation/chunk/en/refdocs/fblangref40/fblangref40-functions-datetime.html#fblangref40-scalarfuncs-datediff

[mrotteveel]

I forgot to leave a comment here: I have updated the documentation.