Date: prev next · Thread: first prev next last
2016 Archives by date, by thread · List index


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

Context


Privacy Policy | Impressum (Legal Info) | Copyright information: Unless otherwise specified, all text and images on this website are licensed under the Creative Commons Attribution-Share Alike 3.0 License. This does not include the source code of LibreOffice, which is licensed under the Mozilla Public License (MPLv2). "LibreOffice" and "The Document Foundation" are registered trademarks of their corresponding registered owners or are in actual use as trademarks in one or more countries. Their respective logos and icons are also subject to international copyright laws. Use thereof is explained in our trademark policy.