Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

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

Closed
firebird-automations opened this issue Jul 31, 2007 · 10 comments

Comments

@firebird-automations
Copy link
Collaborator

Submitted by: @asfernandes

Add MILLISECONDS handling to EXTRACT, DATEADD and DATEDIFF functions.

Commits: b5a23c1

@firebird-automations
Copy link
Collaborator Author

Modified by: @asfernandes

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

resolution: Fixed [ 1 ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @pcisar

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

@firebird-automations
Copy link
Collaborator Author

Commented by: @pmakowski

Q/A test ok

@firebird-automations
Copy link
Collaborator Author

Modified by: @pmakowski

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

@firebird-automations
Copy link
Collaborator Author

Modified by: @pavel-zotov

QA Status: No test

@dmitry-lipetsk
Copy link
Contributor

@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
Copy link
Contributor

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
Copy link
Member

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
Copy link
Member

But DATEDIFF does not support "fractional value for MILLISECOND"

Please create a new issue for that.

@mrotteveel
Copy link
Member

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

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Projects
None yet
Development

No branches or pull requests

4 participants