How to do an addition of times

[LeroyG]

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.

[Wanderer]

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…

[RobertG]

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 …

[Reminard]

Hi! Sorry for the mistakes…

I’m using Base, I assume the latest version since I’ve donwloaded it from the official website.

[Reminard]

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…

[RobertG]

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)

[Villeroy]

Questions about poor sorting

It is absolutely necessary to know the actual database software that is used with Base. LibreOffice can generate 2 types of embedded databases. Both embedded databases are “installed” temporarily from the document when you access the database. The database software you are dealing with is always indicated in the status bar of your document window. [embeddedHSQL_statusbar] [Embedded_Firebird_statusbar] [MySQL_statusbar1] [ODBC_statusbar] However, the vast majority of database documents is co…

[Reminard]

That worked fine!! Thank you so much!

[Villeroy]

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"

[Reminard]

Just one thing I’ve noticed, though: when hours are less then 1, the query always gives 1 as default.

[Villeroy]

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

[RobertG]

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.

[Reminard]

Thanks again both @Villeroy and @RobertG !

[Reminard]

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”]

[Villeroy]

Your database does not contain a table nor query with the name qry_sum_seconds.

[Reminard]

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

[cpb]

@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

[RobertG]

@Reminard

Wrong data type: java.lang.NumberFormatException: Error at index 1 in: “0.0” in statement

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"

[Villeroy]

@RobertG

@Villeroy has recognized this

It was the first lucky shot.

[cpb]

I first investigated the issues raised in this topic regarding HSQL embedded (ROUNDING and use of FLOOR) many years ago.
since when did 31038 seconds = 9hr 37 min 18 secs?
this is the second time an incorrect answer has been tagged ‘Solved’ and that misleads and confuses other users.
.
Once the issues are understood then the coding is easy.
the code I posted (one query) shows the sum of seconds in 3 different formats.
it’s 100% accurate, contains no unnecessary code, is modular and transparent.
.
I don’t like to jump in and post when someone else is addressing an issue because that often leads to confusion, but equally when an accepted answer is incorrect/inaccurate then it’s in the interest of other users that this is highlighted.