CODEWITHSUNDEEP
informix
ktm300xc
ktm300xc

Reputation: 11

How to clean up SQL expression

The code below compares recv.rcpt_dtim (which is a datetime type) against the current date/time. It calculates an elapsed time resulting in hours and minutes formatted like: "04:22". It took me a while to get it functional, which it is, but it just seems sloppy. Does anyone have any tips to clean it up?

TRIM((((CURRENT YEAR TO SECOND - recv.rcpt_dtim)::INTERVAL SECOND(9) to 
SECOND)/3600)::VARCHAR(12) || ':' || CASE WHEN (MOD(MOD(((CURRENT YEAR TO 
MINUTE - recv.rcpt_dtim)::INTERVAL MINUTE(9) to 
MINUTE)::VARCHAR(12)::INT,60),60))<10 THEN "0" ELSE "" END || 
(MOD(MOD(((CURRENT YEAR TO MINUTE - recv.rcpt_dtim)::INTERVAL MINUTE(9) 
to MINUTE)::VARCHAR(12)::INT,60),60))::VARCHAR(12))

Upvotes: 1

Views: 45

Answers (1)

Lu&#237;s Marques
Lu&#237;s Marques

Reputation: 1451

Using Informix 12.10.FC12 Developer Edition I can do the following:

CREATE TABLE test_time
(
    rcpt_dtim DATETIME YEAR TO SECOND
);

INSERT INTO test_time VALUES ( '2019-05-09 10:01:01' );
INSERT INTO test_time VALUES ( '2019-05-09 10:01:59' );
INSERT INTO test_time VALUES ( '2019-05-09 13:01:00' );
INSERT INTO test_time VALUES ( '2019-05-09 15:01:00' );
INSERT INTO test_time VALUES ( '2019-04-02 22:01:00' );

SELECT
    ( CURRENT YEAR TO SECOND - rcpt_dtim )::INTERVAL HOUR(9) TO MINUTE AS elapsed
FROM
    test_time
;


elapsed
  12:47
  12:46
   9:47
   7:47
   0:47
 888:47

Upvotes: 1

Related Questions