I am a beginner in bank Informix , and I am having difficulty calculating the difference between hours in a query.
For the calculation, the fields hrinicio and hrfim should be used
Query I tried:
select *,((DATEDIFF(HOUR, hrinicio, hrfim)) % 24) as total_horas from tabela1
When it comes to the database, regardless of which database is always informed of the version, this makes a lot of difference as the DBs have evolved a lot and have a lot of database installed with extremely old versions out there and consequently with fewer resources.
Anyway, this SQL that you wrote is not valid for Informix because there is no native DATEDIFF function in it.
A good place to ask questions about any bank is your manual.
In this case, you will find information on calculating dates at this link:
link
An information that is missing is also the data type of this time field. For the output of your query, I find it unlikely to be a DATETIME, it should probably be a CHAR.
Anyway the calculation will always be an INTERVAL type and it is a boring type of "convert", to use its result as number, it is always necessary to first convert it to char.
See the example I've assembled below:
drop table if exists tp01;
create temp table tp01 (
hora1 datetime hour to second
,hora2 datetime hour to second
,hora3 char(8)
,hora4 char(8)
)
;
insert into tp01 values ('09:06:55', '14:55:10', '09:06:55','14:55:10');
select * from tp01 ;
select
hora2 - hora1
, (hora2 - hora1)::interval hour(2) to hour
, ((hora2 - hora1)::interval hour(2) to hour)::char(5)
, hora4::datetime hour to second - hora3::datetime hour to second
from tp01
Will return:
hora1 hora2 hora3 hora4
09:06:55 14:55:10 09:06:55 14:55:10
(expression) (expression) (expression) (expression)
5:48:15 5 5 5:48:15