The Wiert Corner – irregular stream of stuff

Jeroen W. Pluimers on .NET, C#, Delphi, databases, and personal interests

  • My badges

  • Twitter Updates

    • RT @michaelbolton: 1) Your periodic reminder: a test is not something we *write*. We write text and programs. A test is an experiment you *… 2 hours ago
    • RT @BiancaPrins: Voor ons allemaal, een #draadje om tot het einde te lezen..... Juist nu (Volgt nog meer in de komende dagen) https://t.co… 2 hours ago
    • RT @McKayMSmith: 90) His entire life, Ben has chosen to place his faith in the law. “Law, not war” has been his motto for decades. “I hav… 2 hours ago
    • RT @McKayMSmith: 82) “It's possible to take the most fundamental, strongly held ideas and change them. What makes people change? Sometimes… 2 hours ago
    • RT @McKayMSmith: 65) “Now I will tell you something very profound, which I have learned after many years. War makes murderers out of otherw… 2 hours ago
  • My Flickr Stream

  • Pages

  • All categories

  • Enter your email address to subscribe to this blog and receive notifications of new posts by email.

    Join 1,640 other followers

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 [WayBacksql – 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 [WayBackdateadd() 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

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

 
%d bloggers like this: