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
Comments
Modified by: @pcisarWorkflow: jira [ 12712 ] => Firebird [ 14151 ] |
Commented by: @pmakowski Q/A test ok |
Modified by: @pmakowskistatus: Resolved [ 5 ] => Closed [ 6 ] |
Modified by: @pavel-zotovQA Status: No test |
One question. Why DATEADD supports "fractional value for MILLISECOND"
And EXTRACT supports "fractional value for MILLISECOND"
But DATEDIFF does not support "fractional value for MILLISECOND"
returns 0 (BIGINT value) ? |
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 :) |
This works for me:
|
Please create a new issue for that. |
@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 |
Submitted by: @asfernandes
Add MILLISECONDS handling to EXTRACT, DATEADD and DATEDIFF functions.
Commits: b5a23c1
The text was updated successfully, but these errors were encountered: