Hello! I’m new to LibreOffice and I’m trying in BASE to use the function SUM to calculate the total duration of a several time fields. The software replies that it’s not possible to do that calculation… does anybody have a way to do it?
Thanks in advance!
Hello @Reminard,
I just changed category to English.
By the way, your question is about Base or Calc?
Please, don’t use Suggest a solution to write something that is not a solution, instead edit your question (below your question, three dots icon, then pencil icon). Thanks.
And please add your version of LibreOffice, wich database you use, and the precise type of the field.
.
I assume you use the default “embedded” HSQLDB and we are at Base. It is tricky, as this version does lack some functions.
Also it depends on the type of data. Unix timestamp is not equal to other dates…
And if you are using Base and have to calculate with time or date: Use the internal Firebird database instead of HSQLDB. There you could set “Time” + 1 as a new “Time” 1 second later.
It is impossible to create a sum of different times directly. You have to switch the times to seconds. Then you could get the sum. And after this you have to get back from seconds to hours, minutes and seconds …
Hi! Sorry for the mistakes…
I’m using Base, I assume the latest version since I’ve donwloaded it from the official website.
I see there’s no direct way to do it, so it’s gonna be tricky for sure.
What @RobertG is suggesting it’s maybe possible through a query? To transform every time into seconds, then make the addition and turn the result again in minutes and seconds? And if so, how do you separate in the time form minutes from seconds so that you can calculate them?
Ej: in my time field I introduce “00:03:45”. Thats must be converted into 225 seconds…
I beg your pardon, but I’m actually very unexperienced with all this stuff…
First: Table, which contains times. “tbl_time” with field “MyTime”
Second: Query for the seconds
SELECT HOUR( "MyTime" ) * 3600 +
MINUTE( "MyTime" ) * 60 +
SECOND( "MyTime" ) AS "MySeconds"
FROM "tbl_time"
Query could be a subquery of the following query:
SELECT SUM( "MySeconds" ) AS "SumMySeconds" FROM "qry_seconds"
Both queries could be subqueries of the following query:
SELECT "SumMySeconds" / 3600 AS "MyHours",
MOD( "SumMySeconds", 3600 ) / 60 AS "MyMinutes",
MOD( MOD( "SumMySeconds", 3600 ), 60 ) AS "MySeconds"
FROM "qry_sum_seconds"
and all this queries could be subqueries of the last query:
SELECT CAST( "MyHours" || ':' || "MyMinutes" || ':' || "MySeconds" AS TIME )
AS "HMS" FROM "qry_sum_HMS"
Note: It will be shown as a decimal number in query editor this way, but it is a time value. So you could extract hours, minutes and seconds from this value later.
Be careful with last query. Set COALESCE or IFNULL, if there might be empty fields.
AddingTimes.odb (4.2 KB)
That worked fine!! Thank you so much!
For some reason I don’t know, your last query returns 09:37:18 which is one hour off. The correct sum is 8:37:18.
EDIT: Conversion to integer cuts off the decimal fraction.
SELECT cast(1.000*"SumMySeconds" / 3600 AS INT) AS "MyHours", cast(MOD( "SumMySeconds", 3600 ) / 60 AS INT) AS "MyMinutes", MOD( MOD( "SumMySeconds", 3600 ), 60 ) AS "MySeconds" FROM "qry_sum_seconds"Just one thing I’ve noticed, though: when hours are less then 1, the query always gives 1 as default.
It is a rounding problem. CAST( “A”/“B” AS INT) cuts off decimal fractions.
The documentation of the database you are actually using is here: Chapter 9. SQL Syntax
Changed the rounding problem in another way:
Second query:
SELECT SUM( "MySeconds" ) * 1.00 AS "SumMySeconds" FROM "qry_seconds"
“SumMySeconds” will be decimal with 2 places - one will also work …
Third query:
SELECT FLOOR("SumMySeconds" / 3600) AS "MyHours",
FLOOR(MOD( "SumMySeconds", 3600 ) / 60) AS "MyMinutes",
FLOOR(MOD( MOD( "SumMySeconds", 3600 ), 60 )) AS "MySeconds"
FROM "qry_sum_seconds"
Floor specifies the largest integer that is not greater than the decimal values of the calculation.
It won’t make any difference in the result @Villeroy posted.
This one returns an error
Table not found in statement [SELECT cast(1.000* “SumMySeconds” / 3600 AS INT) AS “MyHours”, cast(MOD( “SumMySeconds”, 3600 ) / 60 AS INT) AS “MyMinutes”, MOD( MOD( “SumMySeconds”, 3600 ), 60 ) AS “MySeconds” FROM “qry_sum_seconds”]
Your database does not contain a table nor query with the name qry_sum_seconds.
Yeah, I read that and was pretty wrong, the query was there but I’ve been forced to delete and do it from scratch and now it seems to work again… and now is the last query the one stopping. Might it be possible that there’s a problem when the turning into time format again encounters a “0.0” for the hours? (I’m using @RobertG 's solution)
Wrong data type: java.lang.NumberFormatException: Error at index 1 in: “0.0” in statement
@Reminard,
not sure whether this is solved or not, if it is then my apologies.
ELSE:
using the database “AddingTimes.odb” uploaded by RobertG.
paste this into ‘Queries>Create Query in SQL View’
activate the toolbar icon ‘Run SQL command directly’
hit execute.
select
t.secs,
cast(t.secs as int) / 3600 "Hr",
mod(t.secs, 3600) / 60 "Min",
mod(t.secs, 60) "Secs",
cast(t.secs as int) / 3600 || ' Hr '
||
mod(t.secs, 3600) / 60 || ' Min '
||
mod(t.secs, 60) || ' Secs'
"Sum_Time",
cast(
cast(t.secs as int) / 3600 || ':'
||
mod(t.secs, 3600) / 60 || ':'
||
mod(t.secs, 60)
as time
) "RealTime"
from
(
select
sum(hour("MyTime") * 3600 + minute("MyTime") * 60 + second("MyTime")) secs
from
"tbl_time"
) t
Indeed. @Villeroy has recognized this and changed the type back to integer:
Third query will be better this way:
SELECT CAST( "SumMySeconds" / 3600 AS INTEGER ) AS "MyHours",
CAST( MOD( "SumMySeconds", 3600 ) / 60 AS INTEGER ) AS "MyMinutes",
CAST( MOD( MOD( "SumMySeconds", 3600 ), 60 ) AS INTEGER ) AS "MySeconds"
FROM "qry_sum_seconds"
It was the first lucky shot.