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 <[email protected]> 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 < > [email protected]> 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: [email protected] >> 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 -- To unsubscribe e-mail to: [email protected] 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
