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

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

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