Re: [libreoffice-users] LibreOffice Base -Queries...
Hi zed, you have to set the filter in subquery. Could be this is what you want to get: SELECT SUM( "No" ) FROM (SELECT * FROM "Reactor_Videos Table 1" WHERE "First" >= '2020-12-01' AND "Last" <= '2020-12-31' ) You have to set the dates SQL-like in format '-MM-DD' Regards Robert -- Homepage: https://www.familiegrosskopf.de/robert -- To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette List archive: https://listarchives.libreoffice.org/global/users/ Privacy Policy: https://www.documentfoundation.org/privacy
[libreoffice-users] LibreOffice Base -Queries...
I have a database Reactors_Videos and three of the fields are: No (Field Type = Number (Numeric) First (Field Type = Date (DATE) Last (Field Type = Date (DATE) I successfully created a Query to display the total number of Reactions. This Query takes the form SELECT SUM( "No" ) FROM "Reactor_Videos Table 1" I am now trying to create a Query which will display the number of reactions between any two dates but failing miserably Most of them return the error : SQL Status: HY000 Error code: 1000 Syntax error in SQL statement" and an internet search reveals that this error is returned for any number of reasons, none of which are helpful to me (perhaps because of my lack of knowledge) My latest attempt was SELECT SUM( "No" ) FROM "Reactor_Videos Table 1" HAVING ( ( COUNT( "First" ) = 1 / 12 / 20 AND COUNT( "Last" ) = 31 / 12 / 20 ) ) This saved without any error messages but running the query results in no output As will be apparent, my knowledge of the SQL language is minimal at best and any help members are able to offer will be very welcome. Zed -- To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette List archive: https://listarchives.libreoffice.org/global/users/ Privacy Policy: https://www.documentfoundation.org/privacy
Re: [libreoffice-users] Calculating the difference between two time values by means of SQL
Hi,
>
>
> SELECT ID, t1, t2, t2 - t1 AS duration FROM time_measurements1
You are looking for the function DATEDIFF(string, datetime1, datetime2)
Could be this works, if you have choosen a datetime field, not only a
time field.
SELECT ID, t1, t2, DATEDIFF('hour', t1, t2) AS duration FROM
time_measurements1
>
> Would you like to point further information sources out besides query examples
> which work with dates and time stamps?
You are German, right?
have a look at "Base Handbuch" and also "Base Beispiele". In "Base
Beispiele" is an example für getting times for running or something like
this.
Regards
Robert
--
Homepage: https://www.familiegrosskopf.de/robert
--
To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org
Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette
List archive: https://listarchives.libreoffice.org/global/users/
Privacy Policy: https://www.documentfoundation.org/privacy
Re: [libreoffice-users] Calculating the difference between two time values by means of SQL
Howdy, The salient point here is the version of HSQLdb which comes with LibreOffice is rather old and lacking when it comes to date manipulation, but it can be done and hopefully this post over at ASK dot LibreOfffice will help answer you question: https://ask.libreoffice.org/en/question/75480/how-to-do-basic-arithmatic-with-dates-in-libre-base-queries/ best wishes, Drew On Thu, Mar 4, 2021 at 9:04 AM Markus Elfring wrote: > > Hello, > > Two columns of a table (for the database engine “HyperSQL” within the > application > “LibreOffice Base 7.1.0.3-985.1”) have got the data type “TIME”. > I would like to compute a difference for these fields. > > It seems that the following SQL statement is insufficient so far. > > SELECT ID, t1, t2, t2 - t1 AS duration FROM time_measurements1 > > > I would appreciate your advices. > > Would you like to point further information sources out besides query examples > which work with dates and time stamps? > > Regards, > Markus > > -- > To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org > Problems? > https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ > Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette > List archive: https://listarchives.libreoffice.org/global/users/ > Privacy Policy: https://www.documentfoundation.org/privacy -- To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette List archive: https://listarchives.libreoffice.org/global/users/ Privacy Policy: https://www.documentfoundation.org/privacy
