Base Query SQL Time difference problems

[FKlusmann]

Using Version: 7.1.2.2 (x86) / LibreOffice Community - Windows 10.0

Embedded HSQLDB with a Query example from this form gives an interesting correct answer to the “EndTime - StartTime” question: 4::00 PM - 1:15 PM shows 3 hours LESS 15 minute where one would expect to see 2:45.
Embedded Firebird doe not recognize HOUR or MINUTE, and I have not understood the Language Reference. Please point me in the right direction. Thank you. My Query:

![/* Based on ask.libreoffice.org/en/question/48800/base-report-time-difference-function/ */
SELECT "StartTime", "EndTime", 
CONCAT( CONCAT( "hours", ':' ), ( "minutes" - ( "hours" * 60 ) ) ) "DiffTime"
FROM ( 
  SELECT "PriKey", "StartTime", "EndTime", ( HOUR( "EndTime" ) - HOUR( "StartTime" ) ) * 60 + 
    ( MINUTE( "EndTime" ) - MINUTE( "StartTime" ) ) "minutes", 
  ( ( HOUR( "EndTime" ) - HOUR( "StartTime" ) ) * 60 
    + ( MINUTE( "EndTime" ) - MINUTE( "StartTime" ) ) ) / 60 "hours"
  FROM "Table1" 
) AS "subquery"

Firebird is on the left, HSQLDB is on the right.

[Ratslinger]

Hello,

Please verify images (and other attachments) before posting. Can be sen in the Preview section. Place item on its’ own line. For your image you had deleted important posting information.

[FKlusmann]

Yes, sir,I will.

[Ratslinger]

Hello,

The link you provided has an error in the SQL. It incorrectly configures the minutes when end is less than start.

Firebird has different syntax for this. You should refer to the documentation (have given you link previously) for each different database. A is not necessarily = to B.

In Firebird see → How to calculate hours, minutes or seconds between two time values?

This will give you a starting point for Firebird:

SELECT "StartTime", "EndTime", "Hours" || ':'|| "Seconds"
FROM ( 
Select "PriKey","StartTime", "EndTime", floor(("EndTime" - "StartTime") / 3600) "Hours", cast((("EndTime" - "StartTime") - floor(("EndTime" - "StartTime") / 3600) * 3600) / 60 as Integer) "Seconds" from "Table1") AS "subquery"

Resulting in:

Only part left for you to work out is the 00 for minutes when there is none (now displaying just 0).

[FKlusmann]

Thank you again, @Ratslinger !

[FKlusmann]

Referring to your "You should refer to the documentation (have given you link previously) ", and after looking at the very to left-hand of my image, please let me know where I should be referring now. Thank you again, @Ratslinger.

[Ratslinger]

In the Firebird 3.0 Reference Manual, page 32, section 3.4.2. TIME:

EXTRACT (HOUR FROM DATE_FIELD)
EXTRACT (MINUTE FROM DATE_FIELD)
EXTRACT (SECOND FROM DATE_FIELD)

I chose to present a different method which would be not as prone to error as the linked method.

Edit:

Also, since the manual is a PDF document, most viewers have a Find facility. This will return the same result (search HOUR) and more.

[Ratslinger]

Also able to reduce the SQL for Firebird:

Select "PriKey","StartTime", "EndTime", floor(("EndTime" - "StartTime") / 3600) || ':' || cast((("EndTime" - "StartTime") - floor(("EndTime" - "StartTime") / 3600) * 3600) / 60 as Integer) from "Table1"

[FKlusmann]

Thank you, @Ratslinger. Yes, I used the “Find”, and in version 4.0 EXTRACT is at 8.4.3 - page 497.
EXTRACT never crossed my mind, and this is the first I recall seeing FLOOR. I’ve not found a Firebird tutorial which has been very helpful to me.

[Ratslinger]

EXTRACT is not the key; TIME and HOUR are.

Firebird embedded version is 3.0

Edit:

And this is possibly the first time I used floor. Needed to drop the fractions and this is what I found. Sometimes you just need to glance through all the pages in a manual. It gets tougher when dealing with more than one DB (worked with at least half dozen here). Syntax can vary.

[FKlusmann]

I have learned that Base, HSQLDB, and Firebird handle DATE, TIME, and INTERVAL as the same. Therefore one needs to look for DATE operations to work with TIME. @Ratslinger provided solutions for Firebird, and this works for HSQLDB.

SELECT “PriKey”, “StartTime”, “EndTime”,
DATEDIFF(‘MINUTE’,“StartTime”,“EndTime”) / 60 AS “LapsedHrs”,
DATEDIFF(‘MINUTE’,“StartTime”,“EndTime”) AS “LapsedMins”,
DATEDIFF(‘MINUTE’,“StartTime”,“EndTime”) / 60.00 AS “LapsedHrsDecimal”,
(DATEDIFF(‘MINUTE’,“StartTime”,“EndTime”)) - ((DATEDIFF(‘MINUTE’,“StartTime”,“EndTime”) / 60) * 60) AS “MoreMins”,
(DATEDIFF(‘MINUTE’,“StartTime”,“EndTime”) / 60) || ‘:’ || ((DATEDIFF(‘MINUTE’,“StartTime”,“EndTime”)) - ((DATEDIFF(‘MINUTE’,“StartTime”,“EndTime”) / 60) * 60)) As “ElapsedTime”
FROM “Table1”