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: usedateadd
, 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