Hi all,

I wanted to report back, as I finally got to work this out.  I have to
include a lunch break in my calculations, so here is what I did, based on
the examples offered by Bruce and Robert:

SELECT "day_start", "lunch_start", "lunch_end", "day_end", DATEDIFF(
'minute', "day_start", "day_end" ) / 60.00 AS "whole_day", (DATEDIFF(
'minute', "day_start", "day_end" ) - DATEDIFF('minute', "lunch_start",
"lunch_end")) / 60.00 AS "diff_hours" FROM "tTimeEntry"

The result:
*qTotalHours_Example*
day_start lunch_start lunch_end day_end diff_hours
08/15/16 06:30 AM 08/15/16 11:00 AM 08/15/16 12:00 PM 08/15/16 06:00 PM 10.5
08/16/16 06:30 AM 08/16/16 11:00 AM 08/16/16 12:00 PM 08/16/16 03:15 PM 7.75
08/17/16 06:30 AM 08/17/16 11:00 AM 08/17/16 12:00 PM 08/17/16 03:15 PM 7.75
08/18/16 06:30 AM 08/18/16 11:00 AM 08/18/16 12:00 PM 08/18/16 03:15 PM 7.75
08/19/16 06:30 AM 08/19/16 11:00 AM 08/19/16 12:00 PM 08/19/16 03:15 PM 7.75










Or the more "textual" version:
day_start                  lunch_start
lunch_end                 day_end                   diff_hours
08/15/16 06:30 AM    08/15/16 11:00 AM    08/15/16 12:00 PM    08/15/16
06:00 PM    10.5
08/16/16 06:30 AM    08/16/16 11:00 AM    08/16/16 12:00 PM    08/16/16
03:15 PM    7.75
08/17/16 06:30 AM    08/17/16 11:00 AM    08/17/16 12:00 PM    08/17/16
03:15 PM    7.75
08/18/16 06:30 AM    08/18/16 11:00 AM    08/18/16 12:00 PM    08/18/16
03:15 PM    7.75
08/19/16 06:30 AM    08/19/16 11:00 AM    08/19/16 12:00 PM    08/19/16
03:15 PM    7.75

I think I can work out the other calculations I need based on this bit.
Many thanks for the help and examples.

Regards,
Don


On Fri, Aug 26, 2016 at 7:26 PM, Don Parris <parri...@gmail.com> wrote:

> Many thanks to Robert and Bruce.  I will try this as I have time over the
> weekend, and report back how it goes.
>
> On Thu, Aug 25, 2016 at 7:15 PM, Bruce Hohl <bruceh...@gmail.com> wrote:
>
>> 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)
>>
>>
>> On Thu, Aug 25, 2016 at 3:29 PM, Robert Gro├čkopf <
>> rob...@familiegrosskopf.de> wrote:
>>
>>> -----BEGIN PGP SIGNED MESSAGE-----
>>> Hash: SHA1
>>>
>>> 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
>>> -----BEGIN PGP SIGNATURE-----
>>> Version: GnuPG v2
>>>
>>> iQIcBAEBAgAGBQJXv0czAAoJELDKYwaoS9AI28IQAKRBFUlzHX28fOBjp+pAGWeH
>>> 9JnaNSJl0ao2bC1uKjpgI7dN/osbmA6Muj99RIdSpict6goXu+zIKNLTlzw/wSeO
>>> 6w3Xvk3HD/sr4ow4BTQV/2n8A05dHKsIaZnYB8YCjz6d7XEzwxdEyca2zM+3U9KU
>>> LuKNhvbPNpzzPq41p2dAWk8gA99fKEhhFpefsCk8FyW7d1+Rr/+DcqrYn+hY/qRo
>>> UVuNpxr0EOZT4Qa2/c5uXE9DPmKnjo2Q93rvrL1ROO3Xj/Tzi5JexDPaQ4f52YsI
>>> FXHQ6g7PxzDKM5NZ0pKlRlcJqg/9Sv01v75PuuQM9+Sq56LQpImyk99GTO0sJbsM
>>> Hu3bwnHzEy3Up+au0Ra7jJ/cPkkXdgrJYQUUZJ32CSWlaiFQK9PizsfnzlA4OWRn
>>> 6s9O1F3zoDhs50dsERsgLYn4oN0AfFocR4Np9jVPBgYKzhe2yhdem3nZjFwqCjeY
>>> z1L+sNtu6zTikqrGBem8+U14Ti7ogLRdF7pe7X4ONvYAiYP3PXd5DoOcFwr8MRVg
>>> 4BybEk8pOYzzzeGzrTKaJFA1ECQy/l8B0mS4CzKHlDeA5t9WxnuIr3TiCO0KaHya
>>> eTwdyxA5Mhv82t+Egptb/MP2kLArO9trHakM02QkbL64A3g1qlo8CIAzAudu9QrC
>>> KG5LqIEUIeMows6DOgKq
>>> =49Q8
>>> -----END PGP SIGNATURE-----
>>>
>>> --
>>> To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org
>>> Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-uns
>>> ubscribe/
>>> Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette
>>> List archive: http://listarchives.libreoffice.org/global/users/
>>> All messages sent to this list will be publicly archived and cannot be
>>> deleted
>>>
>>
>>
>
>
> --
> D.C. Parris, FMP, Linux+, ESL Certificate
> Minister, Security/FM Coordinator, Free Software Advocate
> http://dcparris.net/ <https://www.xing.com/profile/Don_Parris>
> <http://www.linkedin.com/in/dcparris>
> GPG Key ID: F5E179BE
>



-- 
D.C. Parris, FMP, Linux+, ESL Certificate
Minister, Security/FM Coordinator, Free Software Advocate
http://dcparris.net/ <https://www.xing.com/profile/Don_Parris>
<http://www.linkedin.com/in/dcparris>
GPG Key ID: F5E179BE

-- 
To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org
Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette
List archive: http://listarchives.libreoffice.org/global/users/
All messages sent to this list will be publicly archived and cannot be deleted

Reply via email to