Hi.
I’m trying to use the TIMEDIFF() function to calculate difference between 2 hours but it’s doesn’t work.
SELECT TIMEDIFF(POS_HRS_D, POS_HRS_A) AS POS_HRS FROM T_POINTAGES_SUB
Error message: Access is denied: TIMEDIFF in statement etc…
POS_HRS_D (derparture) and POS_HRS_A (arrival) are in TIME datatype in the table.
Thanks.
Where do you get that TIMEDIFF function from? It looks as if your database engine does not support any such function. You don’t tell us anything about the database you are using with Base.
I use the HSQLDB embedded. I found this function here:
And for MySql:
After seaching, this function doesn’t exist in HSQLDB:
http://hsqldb.org/doc/guide/builtinfunctions-chapt.html#bfc_datetime_functions
How do I get the difference between two hours?
DATEDIFF returns nothing. This is normal, since I only have hours in these two columns, not dates.
Try
SELECT DATEDIFF('hh',POS_HRS_D, POS_HRS_A) AS POS_HRS FROM T_POINTAGES_SUB
will give the difference in hours here.
Have a look at the available functions of your database. HSQLDB 1.8 is your database, not MySQL. You could use Appendix B Comparison of HSQLDB and Firebird of the Base Guide to get the right function.
Test with minute:
08:15:00 and 12:00:00
DATEDIFF('minute', "POI_HRS_A", "POI_HRS_D")
Result : 225
It’s ok, but if I try:
(DATEDIFF('minute', "POI_HRS_A", "POI_HRS_D")/60)
The result, 3, is integer and truncated.
(DATEDIFF('minute', "POI_HRS_A", "POI_HRS_D")/60.0000) calculates with 4 decimal digits.
In order to get a human readable time span in a form or report, the following trick works well enough for hours and minutes:
DATEDIFF('minute', "POI_HRS_A", "POI_HRS_D")/1440.0000
The resulting number represents a fraction of a day as it would be stored in a spreadsheet. When you format to the form control or report control with number format [HH]:MM, this day fraction will be displayed as the correct time. You can also calculate with this number adding days, hours (h/24), minutes(m/1440). HSQL 1.8 has no DATADD function.
Something like (bad solution)datediff('hour',"A","B") || ':' || MOD(datediff('minute',"A","B"),60) displays a concenated text.
With / 1440.0000, the results can be strange:
From 08:00 to 12:00 → 04:00 ok.
But:
From 08:30 to 12:00 → 03:29 ???
From 08:15 to 12:00 → 03:44 ???
@Primus,
if POS_HRS_D is always <= POS_HRS_A (i.e. same day) then something like this should suffice.
if POS_HRS_D can be > POS_HRS_A (i.e. spans 2 days) then it’s a little more complex.
select
POS_HRS_D, POS_HRS_A,
cast(hour(POS_HRS_A) * 60 + minute(POS_HRS_A) - hour(POS_HRS_D) * 60 - minute(POS_HRS_D) as int) / 60 hr,
mod(hour(POS_HRS_A) * 60 + minute(POS_HRS_A) - hour(POS_HRS_D) * 60 - minute(POS_HRS_D), 60) mn,
cast(hour(POS_HRS_A) * 60 + minute(POS_HRS_A) - hour(POS_HRS_D) * 60 - minute(POS_HRS_D) as int) / 60
|| ':' ||
right('0' ||
mod(hour(POS_HRS_A) * 60 + minute(POS_HRS_A) - hour(POS_HRS_D) * 60 - minute(POS_HRS_D), 60)
, 2)
"CONCATENATED"
from
T_POINTAGES_SUBPOS_HRS_A is always > at POS_HRS_D. The hours are always into the same day.
so the code works right?
Dear Mr. Primus,
Why not switching your chrono to seconds:
SELECT DATEDIFF(‘SECOND’, “start”. “end”) from YOURTABLE.
Did I make myself clear?
Thanks for this code, which works, but is not usable in practice. The dates must be entered in the corresponding sub-form, and with such a complex query, entry is impossible. What’s more, the total must be counted and it’s by formatting the query in decimal that it’s easiest.
What would be different in seconds?
You will need more decimal places. Try with / 1440.00000000
At least 14 decimals are required to get the correct display.