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