Time Calculations in LOBase

users
#1

Hi all,

I need to do some time calculations in LO Base queries - seems fairly
straightfoward, but I can't seem to find any good info on how this is done.

I am calculating hours worked in a day (could span midnight, but that is
not normally the case). In a certain app similar to Base, I can do:
(([day_end]-[day_start])-([lunch_end]-[lunch_start]))

In LO Base, the query fails on a wrong data type failure. I am using the
Date/Time data type (time stamp). Can anyone point me to good information
on how to perform this calculation in Base?

Many thanks in advance!
Don

#2

It occurs to me my OP may not have been as clear as it should be. To
clarify, I need to calculate the decimal value of the times involved:
(([day_end]-[day_start])-([lunch_end]-[lunch_start])) = H.nn worked

So, for example: ((17:00-08:00)-(13:00-12:00)) = 8.0

I don't believe the DATEDIFF function gives me what I am looking for, but
maybe it can with some extra work. I was pretty sure I would find some
examples of this type of calculation out there.

#3

What type of database is used - HSQLDB embedded, HSQLDB, MySQL, PostgreSQL,
etc?

#4

Just using the HSQLDB embedded.

#5

Hi Don,

It occurs to me my OP may not have been as clear as it should be.
To clarify, I need to calculate the decimal value of the times
involved: (([day_end]-[day_start])-([lunch_end]-[lunch_start])) =
H.nn worked

So, for example: ((17:00-08:00)-(13:00-12:00)) = 8.0

This one works:
SELECT "ID", "DayBegin", "DayEnd", "LunchBegin", "LunchEnd",
DATEDIFF('hh',"DayBegin", "DayEnd")-DATEDIFF('hh',"LunchBegin",
"LunchEnd") AS "Worked" FROM "Times"

Examples I have written down in Base-Handbook. But the newer versions
aren't translated yet from German to English.

Be careful with the example. It does only work with the value for the
hour. So you get the same with
(17:00-08:00)-(13:00-12:00)
and for example
(17:10-08:00)-(13:00-12:00)

So it would be better to write down the same code for minutes:
SELECT "ID", "DayBegin", "DayEnd", "LunchBegin", "LunchEnd",
DATEDIFF('mi',"DayBegin", "DayEnd")-DATEDIFF('mi',"LunchBegin",
"LunchEnd") AS "Worked" FROM "Times"

So you get the workingtime in minutes. Devide it with 60.00 (.00 for
the decimal places) and you get a better diff for hours than in the
first query.

Regards

Robert

#6

I can confirm as follows.

With this in table1:
ID time1 time2
1 01/01/16 11:30 PM 01/02/16 04:00 AM
2 01/02/16 01:30 AM 01/02/16 05:30 AM
This query:
SELECT "time1", "time2", DATEDIFF( 'minute', "time1", "time2" ) AS
"diff_minutes", DATEDIFF( 'minute', "time1", "time2" ) / 60.00 AS
"diff_hours" FROM "Table1"

Returns:
time1 time2 diff_minutes diff_hours
01/01/16 11:30 PM 01/02/16 04:00 AM 270 4.5
01/02/16 01:30 AM 01/02/16 05:30 AM 240 4
This might be helpful:
http://hsqldb.org/doc/guide/builtinfunctions-chapt.html#
bfc_datetime_functions

Be mindful of appropriate use of HSQLDB embedded:
https://wiki.openoffice.org/wiki/FAQ_(Base)

#7

Many thanks to Robert and Bruce. I will try this as I have time over the
weekend, and report back how it goes.