Add MILLISECONDS handling to EXTRACT, DATEADD and DATEDIFF functions [CORE1387]

[firebird-issue-importer]

Submitted by: @asfernandes

Add MILLISECONDS handling to EXTRACT, DATEADD and DATEDIFF functions.

Commits: b5a23c1

[firebird-issue-importer]

Modified by: @asfernandes

status: Open [ 1 ] => Resolved [ 5 ]

resolution: Fixed [ 1 ]

[firebird-issue-importer]

Modified by: @pcisar

Workflow: jira [ 12712 ] => Firebird [ 14151 ]

[firebird-issue-importer]

Commented by: @pmakowski

Q/A test ok

[firebird-issue-importer]

Modified by: @pmakowski

status: Resolved [ 5 ] => Closed [ 6 ]

[firebird-issue-importer]

Modified by: @pavel-zotov

QA Status: No test

[dmitry-lipetsk]

@asfernandes,

One question.

Why 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)

?

[dmitry-lipetsk]

Some research (FB3, dialect3)

select
 CAST
  ((timestamp '9999-12-31 23:59:59.9999' - timestamp '0001-01-01 00:00:00.0000') + timestamp '0001-01-01 00:00:00.0000'
   AS VARCHAR(32)) from rdb$database

works OK, result is 9999-12-31 23:59:59.9999

select
 CAST
  ((timestamp '9999-12-31 23:59:59.9999' - timestamp '0001-01-01 00:00:00.0001') + timestamp '0001-01-01 00:00:00.0001'
   AS VARCHAR(32)) from rdb$database

returns the ERROR: "value exceeds the range for valid timestamps"

---

As I understood, the single correct way for calculation of subtract between timestamps is using an expression like:

DATEDIFF(MILLISECOND FROM timestamp1 TO timestamp2)
   +(  (EXTRACT(MILLISECOND FROM timestamp2) - EXTRACT(MILLISECOND FROM timestamp1))
     - TRUNC(EXTRACT(MILLISECOND FROM timestamp2) - EXTRACT(MILLISECOND FROM timestamp1))  )

?

As example:

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

works correctly and returns 9999-12-31 23:59:59.9999

:)

[asfernandes]

select
 CAST
  ((timestamp '9999-12-31 23:59:59.9999' - timestamp '0001-01-01 00:00:00.0001') + timestamp '0001-01-01 00:00:00.0001'
   AS VARCHAR(32)) from rdb$database

This works for me:

CAST                             
================================ 
9999-12-31 23:59:59.9999         

[asfernandes]

But DATEDIFF does not support "fractional value for MILLISECOND"

Please create a new issue for that.

[mrotteveel]

@dmitry-lipetsk Please create new issues to report bugs, don't discuss them on older issues. Also, discussions are better than on firebird-devel than in issues comments.

In any case, the 100 microsecond precision for MILLISECOND for DATEADD was added at a later time, through feature request #4777.