[Solved] SQL Query: Need "Time" + "MinuteLen" as "EndTime"

[Panaiotis82]

I have a database of student lesson records. The Lessons table includes a lesson start time and a length in minutes. How do I add a time field with the (start time + length)?

This is the current relevant parts of the SQL query without an attempted end time:
SELECT "Lessons"."Date",
"Lessons"."Lesson.Time" AS "Time",
"Lessons"."Length",
FROM "Lessons", "Students" WHERE "Lessons"."Date" >= CURRENT_DATE

Embedded database: HSQL database engine

[Villeroy]

Embedded HSQL does not have a DateAdd function like most other database engines have. As a work-around, you can calculate the day fractions and add them as you would do in a spreadsheet. A formatted control on a form or report can display this value as a time.

Code: Select all

SELECT *,
Hour("Time")/24 + Minute("Time")/1440 + Hour("Length")/24 + Minute("Length")/1440 AS "DayFraction"
FROM "Lessons"

[Panaiotis82]

After 4 hours of failed attempts to use and modify your example, I figured out a solution. First, the Length value is an integer, not a time value, so the Minute("Length") function crashed OO. However, removing the MINUTE() function always returned a value of zero until I added several zeros after a decimal point in the division value. The following example works:

The field types and names are: time: "Time", int: "Length"
The formula that works is:

SELECT "Time", "Length", HOUR( "Time" ) /24.00000 + "Length" /1440.00000 AS "End" FROM "Lessons"

Then I change the "End" column to a Time format. This works as long as the Length value is not blank (NULL?)

[Villeroy]

Any operation with a Null value returns Null.

[Panaiotis82]

Yes, I see that. Thanks for your help. I've been trying for years to solve the time issue: literally. I will try something similar for dates. Hpefully I will be able to work out the details on my own.

[Panaiotis82]

I reset this as unsolved because the solution only partially works, and has exposed anomalous and finicky behavior. While I was able to get something to work under extremely limited conditions, implementing it in the necessary or any usable context consistently fails.

The SQL editor requires the formula to be without spaces: e.g. HOURS("Time")/24.00000. However, after closing the editor and re opening it, the spaces are added back and have to be removed before saving any edits.

Worse than that, this formula doesn't work in a table form at all.

In a query, OO crashes if the query includes references to a linked table. These references work if there are no calculated fields, so I can either have the calculation or the reference fields, but not both.

There is probably some magic combination that will provide an acceptable solution, but I have been unable to find it, and even systematically trying different combinations has not gotten me any closer.

[chrisb]

original post deleted by author.

i had uploaded an attachment which demonstrated an alternative formula in query & form.
the attachment was never downloaded by Panaiotis82 so therefore i have withdrawn my contribution.

[Mountaineer]

..."
The formula that works is:
SELECT "Time", "Length", HOUR( "Time" ) /24.00000 + "Length" /1440.00000 AS "End" FROM "Lessons"

Then I change the "End" column to a Time format. This works as long as the Length value is not blank (NULL?)

Functions IFNULL or COALESCE can provide an alternative value for NULL like

Code: Select all

SELECT "Time", "Length", HOUR( "Time" ) /24.00000 + 
COALESCE("Length" /1440.00000, 0) AS "End" FROM "Lessons"

Another way around this would be to provide a default value for the column, then you may even set it to NOT NULL.

[Mountaineer]

....
The SQL editor requires the formula to be without spaces: e.g. HOURS("Time")/24.00000. However, after closing the editor and re opening it, the spaces are added back and have to be removed before saving any edits.
...

Never seen that, but I'm using LibreOffice.
If you wish to keep your precise query, you may set "direct SQL"-mode. (Rightmost icon, if I'm remembering right). You can't use :param then, but OpenOffice should not change a single character in your query.

[Mountaineer]

...
In a query, OO crashes if the query includes references to a linked table. These references work if there are no calculated fields, so I can either have the calculation or the reference fields, but not both.
...

Show the code here, so one can check, because this is not a real restriction of Base, even if you may have found a bug.

Work-arounds: Try direct SQL, if the cause is Base when it interprets your query or save a working query as View in the database (e.g. with calculated fields), then add the reference fields using the view as datasource.

[Panaiotis82]

For some inexplicable reason OO is behaving better today. Here is the SQL line that works:

SELECT "Lessons"."Was.Taught" AS "Taught", "Students"."Name.first", "Students"."Name.last", "Lessons"."Date" AS "Date",
"Lessons"."Lesson.Time" AS "Time", "Lessons"."Length" AS "Length",
HOUR( "Lesson.Time" ) / 24.000000000 + MINUTE( "Lesson.Time" ) / 1440.000000000 + "Length" / 1440.000000000 AS "End",
"Lessons"."Assignments" AS "Assignments", "Lessons"."notes" AS "Notes" FROM "Lessons", "Students", "Date-1" WHERE "Lessons"."Student.ID" = "Students"."Student.ID" AND "Lessons"."Was.Taught" = FALSE AND "Lessons"."Date" >= "Date-1"."First" AND "Lessons"."Date" <= "Date-1"."Last" ORDER BY "Date" ASC, "Time" ASC

Notice how many zeros past the decimal places it takes to give me clean values when the result is formatted as a time.