CODEWITHSUNDEEP
sqlinformixtypeconverter
Erns Bauer
Erns Bauer

Reputation: 15

Transform from interval " 1:05:04" to HH:MM format Informix 11.50

I have a column acdtime that gives me the time on a call in seconds. I need to transform those fields to HH:MM format.

My idea is first to convert the seconds to interval using 3904 as parameter:

INTERVAL(0:0:0) HOUR TO second + acdtime UNITS second

and I get:

1:05:04

Then convert this result to char and then make a substring.
This is where I get stucked.
Any idea?

Thanks a lot

Upvotes: 0

Views: 1372

Answers (2)

RET
RET

Reputation: 9188

Unless you have a very archaic version of Informix, what you want is the TO_CHAR() function, but you need a DATETIME value as the base, not an interval:

TO_CHAR(TODAY::DATETIME YEAR TO SECOND + 3904 UNITS SECOND, '%H:%M')

... which will produce:

(expression)  01:05

Casting TODAY as a DATETIME gives you midnight, to which you can add your count of seconds. Of course, this method assumes no call lasts more than 23:59:59. If that possibility can't be discounted, you may need to wrap the expression in a CASE statement that checks acdtime < 86400.

Upvotes: 1

Jonathan Leffler
Jonathan Leffler

Reputation: 753655

Since you want the times in HH:MM format, what about:

SELECT acdtime,
       EXTEND(TODAY::DATETIME YEAR TO SECOND + acdtime UNITS SECOND,
              HOUR TO MINUTE),
       EXTEND(TODAY::DATETIME YEAR TO SECOND +
              (TRUNC((acdtime + 59)/60)*60) UNITS SECOND,
              HOUR TO MINUTE)
  FROM TheAnonymousTable;

Output:

   0    00:00   00:00
  59    00:00   00:01
  60    00:01   00:01
3904    01:05   01:06

The first expression truncates fractions of a minute downwards, so times from 0 to 59 seconds map to zero minutes. More often, 'time on call' is rounded up to next bigger minute; the second expression does that. Note that this runs into problems if the call duration is 86400 seconds or longer (1 day or more). That is fixable, but it isn't entirely trivial (and definitely not succinct).

Upvotes: 1

Related Questions