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 <parrisdc@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 <brucehohl@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 <
robert@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+unsubscribe@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+unsubscribe@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
Context
- Re: [libreoffice-users] Re: Time Calculations in LOBase (SOLVED) · Don Parris
Privacy Policy |
Impressum (Legal Info) |
Copyright information: Unless otherwise specified, all text and images
on this website are licensed under the
Creative Commons Attribution-Share Alike 3.0 License.
This does not include the source code of LibreOffice, which is
licensed under the Mozilla Public License (
MPLv2).
"LibreOffice" and "The Document Foundation" are
registered trademarks of their corresponding registered owners or are
in actual use as trademarks in one or more countries. Their respective
logos and icons are also subject to international copyright laws. Use
thereof is explained in our
trademark policy.