How to do basic arithmatic with dates in Libre Base queries

English
#1

All I want to do is find the number of entries that are present for a specific day + 1.

The code I have to get the count for a day you select works and is:

SELECT COUNT( “ID” ) FROM “Raw data” WHERE “Date” = :Start_Date

But how do I do this for a date 1 day in the future from the selected date please? All my efforts have failed dismally.

… and if you are feeling generous, how do I get 2 results displated from a single query where I get the day you select and the next day on seporate lines please.

Your help will be very much appreciated.

DATEADD (or similar) in calculated field in view (embedded db)
Unable to get any date functions working to update tables from events in LO 7 base. Basic update works but date functions do not
#2

First is the obvious question. Since you are inputting (via parameter) the date already, why not input a date one day higher?

Situations exist in HSQLDB SQL and LO Base using certain date functions in queries. The following:

SELECT DATE_ADD ( DATE '2016-08-20', INTERVAL 1 DAY ) FROM TEST1

adds one day to the date shown. However, if you are using an embedded database (Base default) it doesn’t work because it is an older more limited version of the DB and the function is not available. If you have a relatively current version in a split db, then the statement works in a query only when running SQL directly. This means you cannot use parameters (as in your example).

Here is a link to the functions available in the embedded version(1.8): click here.

#3

Sadly, your code returns an SQL syntax error. But thanks for trying. Perhaps you could let me know what I got wrong with this?

#4

If you are trying to get one continuous week, use:

SELECT COUNT( "ID" ) FROM "Raw Data"  WHERE ( "Date" >= :start_date) AND ( "Date" <= :end_date) GROUP BY "Date"

Just input the actual start/end dates.

#5

Sorry I didn’t answer your question about the syntax error. Actually it was discussed in my original answer - if using an embedded DB - error; if using split DB with later HSQL version - will only work with Run SQL command directly turned on (in SQL View, toolbar button marked SQL). Whether it works or not depends upon which version of HSQL is being used.

#6

Something else to consider is the OR condition for multiple individual or group dates. Also, When trying the above SELECT statement, make sure you put the correct dates into the parameter being presented, because they are presented alphabetically. So the example will ask for end_date first. If you want, just change :start_date to :begin_date then it will be presented logically.

#7

Thanks for the additional ideas.

Your last bit of code (for the whole week) generates an error after putting in the dates - “The data content could not be loaded. Not in aggregate function or group by clause”

As I am new to Libat and databases in general I am using this as an “out of the box” thing until I get more conversant with it so I will need to be spoonfed more that I’d like. I only know that the version os SQL I am using is the one that comes with LibraOffise 5.

#8

I found out about the presentation thing the hard way about a week ago - it took me all day to realise what was going on when I gos silly or no results. I learned a lot that day!

#9

The only thing I found wrong was the table name had D vs d in it. The error comes from GROUP BY clause missing. Here is statement with both previously mentioned corrections and added “Date” to result for clarity:

SELECT "Date", COUNT( "ID" ) FROM "Raw data" WHERE ( "Date" >= :begin_date) AND ( "Date" <= :end_date) GROUP BY "Date"
#10

This statement works exactly as is in the sample provided by @PYS

SELECT "Date", COUNT( "IdTable" ) FROM "Table" WHERE ( "Date" >= :begin_date ) AND ( "Date" <= :end_date ) GROUP BY "Date"

Use date of begin = 01/01/08 and end = 01/01/17 and the result will return 5 records.

#11

Wow! So simple! Thank you so much.

I did have the knowledge to do this myself - just not the imagination. I think I was too focused on just entering the start date and adding a day, 2 days, 3 day… until I got a weeks worth. Whilst it would be nice to have this functionality (so as to avoid users putting in an end date that is before a start date) would be nice, this is a neat way to get what I need.

Thank you again for your time and patience - to you and everyone who chipped in with advice.

#17

@PYS your solution does not do what the poster @Brian_Green wants.
He wants to enter a date in a parameter query and get a count of the records that have in a date field the next day’s date i.e. the date entered in the parameter plus 1 day.

Your Query1 selects records with the date entered in the parameter not the next day’s date. You create an alias field Date+1 with 1 added to the date. The Query2 counts the records in the Date+1 field of Query1 which is therefore a count of the records which match the parameter date not the parameter date plus 1.

In the Table in your attached database there are no records with a date 1/07/2008. There are 3 with the date 29/06/2008. Entering 28/06/2008 gives 0 and entering 29/06/2008 gives 3. So it is showing the count for the date entered not for the next day.

As it is not possible to modify the value entered in a parameter before the query runs I can’t see a way of doing this with a parameter query. I think a macro would be required to carry out this requirement.

EDITED 23/08/2016

I have created a sample of using a Macro to do what you require. I modified the sample provided by @PYS. Open the form and enter the date 30/07/2016 and it will display the count for that day and the next 2 days. This can be extended to any number of days.

AddOneDayMod.odb

#18

Thanks Peter. This is exactly what I want. If its not possible to do this in a query, then I dont mind Macros - as long as the question is satisfied.

So - the next question I suppose … How do I write macros in Libra Base - and a code that explains a solution to this question would be more than welcome. Whilst I want to learn this stuff, a copy and paste solution would be both generous and nice! … or a pointer to a website that can teach this.

Thanks again for your comments

#19

I obviously did not quite understand the question, sorry :slight_smile:

#20

@Brian_Green I have edited my original post with a sample using a Macro.

#12

Hi

You can use 2 queries as in this AddOneDay.odb example.

  • Query1 selects the records according to the parameter and create a new column by adding one day to the date
  • Query2 counts records from Query1 and display the 2 dates

Query1

SELECT "Date", TO_CHAR( CAST( YEAR( "Date" ) || '-' || MONTH( "Date" ) || '-' || DAY( "Date" ) + 1 AS "DATE" ), 'DD/MM/YYYY' ) "Date+1" 
FROM "Table" WHERE "Date" = :Start_Date

Explanations:

  • the date is split by year, month, day so you can do DAY( "Date" ) + 1
  • CAST converts the expression to DATE format (which is a number)
  • TO_CHAR displays the date in the format of your choice. My interface is in French, so I use ‘DD/MM/YYYY’

Query2

SELECT "Date", MAX( "Date+1" ) "Date + 1", COUNT( "Date+1" ) "Count" FROM "Query1"  GROUP BY "Date"

Explanations:

  • Use of GROUP BY to be able to COUNT
  • Fields used in a “group” query must all apply a function. I use MAX( "Date+1" ) to display this field. Note that “Date+1” is the name of the fied; it is a string, not the formula (calculation is made in Query1)

To test, run Query2 with parameter 30/06/08 (once again my UI is french, adapt if necessary, i.e. MM-DD-YYYY).

The result is 3 for 01/07/2008

Regards

#13

Thanks for this reply. It almost works flawlessly. I use the same date format as you so was able to copy and paste the code directly so I know its not my typing that goes wrong here.

What happens here is that the date does increase by one (as required) but it presents the data from the date inputted. I will try and tweak the code to get it to do what I want, but if you get there first I wont mind :slight_smile:

#14

Also, it seems a bit inefficiant to have 2 queries to do one job. Is it possible to combine these 2 lines of code into one query (like in Visual Basic you use ; between lines of code)

Thanks again for your help.

#15

IMO do not obtain the result that is what would be inefficient :slight_smile:

You can combine the 2 queries but load and performance will be the same. My goal here was clarity and pedagogy.

SELECT "Date", MAX( "Date+1" ) "Date + 1", COUNT( "Date+1" ) "Count" FROM 
(SELECT "Date", TO_CHAR( CAST( YEAR( "Date" ) || '-' || MONTH( "Date" ) || '-' || DAY( "Date" ) + 1 AS "DATE" ), 'DD/MM/YYYY' ) "Date+1" FROM "Table" WHERE "Date" = :Start_Date)
GROUP BY "Date"

Regards

#16

Ah - yes - I see how to combine the two queries - with the FROM and enclose Query 1 in parenthasies. Thanks for that. I bow down to your superior knowledge in the efficiency and I appreciate the clarity of your first post.

The problem remains though - it is still not pulling the data needed from Date+1

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.