Hello.
In a query, I want to calculate an end time based on an arrival time (TIME column) and a duration (DECIMAL column), kind :
SELECT TRA_DEPARTURE, TRA_DURATION, (TRA_DEPARTURE + TRA_DURATION) AS TRA_ARRIVAL
FROM T_TRAVEL
With :
TRA_DEPARTURE TIME
TRA_DURATION DECIMAL (6,2)
Thanks.
Which database?
What is stored in the decimal(6,2)?
Example.
CREATE TABLE T_TRAVEL (
TRA_ID INTEGER GENERATED BY DEFAULT AS IDENTITY (START WITH 1, INCREMENT BY 1) NOT NULL,
EMP_ID INTEGER NOT NULL,
TRA_DEPARTURE TIME NOT NULL,
TRA_DURATION DECIMAL (6,2) NOT NULL,
CONSTRAINT PK_TRA_ID PRIMARY KEY (TRA_ID),
CONSTRAINT FK_EMP_ID FOREIGN KEY (EMP_ID) REFERENCES T_EMPLOYEES (EMP_ID)
);
In TRA_DEPARTURE, I input the hour, example : 14:00:00
In TRA_DURATION, I input the duration, example : 1.5
In the query, after calculation, TRA_DEPARTURE + TRA_DURATION must be : 15:30:00
The SQL looks as if it could be a HSQL database and TRA_DURATION stores hours.
HSQL 2.x includes a DATEADD function, whereas the embedded HSQL 1.8 has only DATEDIFF which is useless in this context.
I can offer a work-around for dates in embedded HSQL. I’ve never tried to do the same with times.
hsql_dates.odb (30.7 KB)
Query “Plus90” adds 90 days to dates in “BD” by calculating DATEDIFF( ‘dd’, ‘1899-12-30’, “BD” ) + 90 which gives a day number that can be interpreted as dates by formatted fields in forms and reports and in Writer/Calc table cells.
EDIT:
add_hsql_date_times.odb (40.3 KB)
@Primus,
the value of time is always between 00:00:00 and 23:59:59.
just for fun I did this using the default embedded hsql.
this sql shows the result of TRA_DEPARTURE + TRA_DURATION in hours and minutes (use direct mode).
add a record to your table: TRA_DEPARTURE TIME = 14:00:00, TRA_DURATION 11.25.
EDIT: updated sql. deleted unnecessary case statement.
select
t.*,
cast(floor(mins / 60) as int) hr,
mod(mins, 60) mn,
cast(cast(floor(mins / 60) as int) || ':' || mod(mins, 60) || ':0' as time) real_time
from
(
select
*,
mod(hour(TRA_DEPARTURE) * 60 + minute(TRA_DEPARTURE) + 60 * TRA_DURATION, 1440) mins
from
T_TRAVEL
) tI prefer to use embedded HSQLDB for compatibility reasons. Your query is short, but there are problems with the results on certain lines, such as 00:29:59 instead of 00:30:00. Especially since if I change the formatting to 00:00 (I don’t need the seconds), the result doesn’t round off but truncates to 00:29.
This request is almost scary! 
It works perfectly, but it’s not going to be easy to integrate the rows into a query already populated with different types of joins and “case when” rows. I’ll give it a try… Thanks too…
You should always mention this. Between HSQL1 and 2 there are more differences than between OpenOffice and LibreOffice or between Excel and Calc.
SELECT "TIMES".*,
CEILING( DATEDIFF( 'second', "D" || ' ' || '00:00:00', "D" || ' ' || "T" ) + "HRS" * 3600.0000 ) / 86400.0000 "TARGET"
FROM "TIMES"@Primus,
I suspect that your query displays the fields TRA_DEPARTURE and TRA_DURATION, if that’s the case then it’s easy to condense my formula into a single line and paste it at the same level, it may look ugly but it will work:
cast(cast(floor(mod(hour(TRA_DEPARTURE) * 60 + minute(TRA_DEPARTURE) + 60 * TRA_DURATION, 1440) / 60) as int) || ':' || mod(mod(hour(TRA_DEPARTURE) * 60 + minute(TRA_DEPARTURE) + 60 * TRA_DURATION, 1440), 60) || ':0' as time) real_timecpb and Villeroy
Ok, both queries work perfectly and are easy to integrate into an already prepared query.
For the time being, however, I’m still in the dark as I’m in the process of sketching out a weekly planning basis with start and end times. And depending on the final layout I want to achieve, I’m still not sure whether it’s better to enter a start and end time and calculate the duration, or to enter a start time and duration, then calculate the end time with your queries.
So for the time being, I’ll leave the topic open.
Thanks again.
This is what DATEDIFF does.
This is what DATEADD would do with other db engines.
You may do both alternatively:
CASE WHEN "Duration" IS NULL THEN DATEDIFF(...) ELSE "Duration" END AS "Duration"
CASE WHEN "End" IS NULL THEN <dateadd expression> ELSE "End" END AS "End Time"
together with
ALTER TABLE T ADD CONSTRAINT "one_or_the other" CHECK (("Duration" IS NULL AND NOT "End" IS NULL) OR ("End" IS NULL AND NOT "Duration" IS NULL))I’ve already used DATEDIFF, but incompletely, as there are still rounding problems.
DATEDIFF('HOUR', DEPARTURE, ARRIVAL) AS DURATION
DEPARTURE : 13:30:00
ARRIVAL : 15:00:00
DURATION : 2
I can use MINUTE instead HOUR, but I don’t know how to convert the type to give hours and minutes, either in hour format (01:30:00) or decimal format (1.5).
In case to calculate the duration with DATEDIFF, it’s now ok with the syntax for a result in decimal.
DATEDIFF('MINUTE', DEPARTURE, ARRIVAL) / 60.00 AS DURATION
TimesDuration.odb (14.9 KB)
A simple odb showing working sample of arrival, departure, and duration times with a form and sql. The form allows the user to manipulate any of the times, including duration! All three fields are stored in the table as Time types.