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

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

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