Calculation of hours in Informix bank

Question

I’m a beginner in banking Informix, and I’m 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

Answer 1

When it comes to database, regardless of which database is always informed version, it makes a lot of difference because the BD has evolved a lot and has a lot of database installed with extremely old versions around and consequently with less resources.

Anyway, this SQL 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 about calculating dates on this link : https://www.ibm.com/support/knowledgecenter/en/SSGU8G_12.1.0/com.ibm.sqlr.doc/ids_sqr_156.htm

One piece of information that is missing is also the data type of this time field. By the output of your query, I find it unlikely that it is a DATETIME, probably it should 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, always need first convert it to char.

See the example I’ve built 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