Re: [libreoffice-users] Re: Time Calculations in LOBase

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