InterBase/Firebird divisions: use E0 syntax to force floating point calculations
Posted by jpluimers on 2019/04/23
I was a bit baffled at the below results because in other other databases.
You need to use this kind of notation ####.0E0 to force an integer to be evaluated as floating point, which is an extra .0 than explained by [WayBack] sql – Why does Firebird truncate decimal places when dividing? – Stack Overflow
I’m not the only person confused by this: [WayBack] [#CORE-2849] Simple mathematics gone wrong – Firebird RDBMS Issue Tracker.
I know that as of Firebird 2.1 you can use the [WayBack] dateadd() function for calculations on timestamps, date or time values (hence the confusing name dateadd), but you don’t always have the delta readily available in one unit (second, minutes, etc) and for a mixed Firebird/InterBase deployment having one kind of logic is often preferred.
select
cast('now' as timestamp) as NowTimeStamp
, (15.0*(1.0/24.0/60.0)) as A15MinutesInDays
, (15.0/24.0/60.0) as B15MinutesInDays
, (15.0E0/24/60) as C15MinutesInDays
, cast('now' as timestamp) + (15.0*(1.0/24.0/60.0)) as AIn15MinutesTimeStamp
, cast('now' as timestamp) - (cast('now' as timestamp) + (15.0*(1.0/24.0/60.0))) as ADifferenceInDays
, (cast('now' as timestamp) - (cast('now' as timestamp) + (15.0*(1.0/24.0/60.0)))) * 24 as ADifferenceHours
, (cast('now' as timestamp) - (cast('now' as timestamp) + (15.0*(1.0/24.0/60.0)))) * 24*60 as ADifferenceMinutes
, cast('now' as timestamp) + (15.0/24.0/60.0) as BIn15MinutesTimeStamp
, cast('now' as timestamp) - (cast('now' as timestamp) + (15.0/24.0/60.0)) as BDifferenceDays
, (cast('now' as timestamp) - (cast('now' as timestamp) + (15.0/24.0/60.0))) * 24 as BDifferenceHours
, (cast('now' as timestamp) - (cast('now' as timestamp) + (15.0/24.0/60.0))) * 24*60 as BDifferenceMinutes
, cast('now' as timestamp) + (15.0E0/24/60) as CIn15MinutesTimeStamp
, cast('now' as timestamp) - (cast('now' as timestamp) + (15.0E0/24/60)) as CDifferenceDays
, (cast('now' as timestamp) - (cast('now' as timestamp) + (15.0E0/24/60))) * 24 as CDifferenceHours
, (cast('now' as timestamp) - (cast('now' as timestamp) + (15.0E0/24/60))) * 24*60 as CDifferenceMinutes
-- Firebird 2.1 and up: use dateadd , dateadd(minute, 15, cast('now' as timestamp)) as DIn15MinutesTimeStamp , cast('now' as timestamp) - dateadd(minute, 15, cast('now' as timestamp)) as DDifferenceInDays , (cast('now' as timestamp) - dateadd(minute, 15, cast('now' as timestamp))) * 24 as DDifferenceHours , (cast('now' as timestamp) - dateadd(minute, 15, cast('now' as timestamp))) * 24*60 as DDifferenceMinutes from rdb$database /* Transposed results are below. Excected values for: A15MINUTESINDAYS 0.0104166666666667 B15MINUTESINDAYS 0.0104166666666667 Transposed results: NOWTIMESTAMP 20-9-2017 20:31:40 A15MINUTESINDAYS 0 B15MINUTESINDAYS 0.01 C15MINUTESINDAYS 0.0104166666666667 AIN15MINUTESTIMESTAMP 20-9-2017 20:31:40 ADIFFERENCEINDAYS 0 ADIFFERENCEHOURS 0 ADIFFERENCEMINUTES 0 BIN15MINUTESTIMESTAMP 20-9-2017 20:46:04 BDIFFERENCEDAYS -0.01 BDIFFERENCEHOURS -0.24 BDIFFERENCEMINUTES -14.4 CIN15MINUTESTIMESTAMP 20-9-2017 20:46:40 CDIFFERENCEDAYS -0.010416667 CDIFFERENCEHOURS -0.250000008 CDIFFERENCEMINUTES -15.00000048 DIN15MINUTESTIMESTAMP 20-9-2017 20:46:40 DDIFFERENCEDAYS -0.010416667 DDIFFERENCEHOURS -0.250000008 DDIFFERENCEMINUTES -15.00000048 */
–jeroen
Leave a Reply